TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

PostgreSQL Where Clause

PostgreSQL Where Clause for beginners and professionals with examples database, table, create, select, insert, update, delete, join, function, index, clause, trigger, view, procedure etc.

<< Back to POSTGRESQL

PostgreSQL WHERE Clause

In this section, we are going to understand the working of PostgreSQL where clause, which is used to filter the rows returned by a Select command.

The WHERE condition is used to describe a condition while we get data from a table or a Join various tables. It is commonly used with SELECT, UPDATE, and DELETE commands to filter the output. It returns the exact result only when the condition is fulfilled.

Note: To select rows that satisfy a detailed condition, we use a WHERE clause. And the SELECT command returns all rows from one or more columns in a table.

The syntax of PostgreSQL WHERE clause

The syntax of PostgreSQL WHERE condition is as below:

SELECT column1, column2, ..... columnN  
FROM table_name  
WHERE [search_condition]  
ORDER BY sort_expression

In the Select command, the WHERE condition comes after the FROM clause. And the condition is used to filter the rows returned from the SELECT command.

The where clause could be the Boolean expression or a grouping of Boolean expressions if we use the AND & OR operators. And the clause must assess to true, false, or unknown.

The WHERE condition works on the below format:

It begins from the From clause → then it performs the Where condition → after that it will Select the particular columns and then perform by the Order by clause.

PostgreSQL WHERE Clause

The WHERE condition does not support the column aliases under the SELECT Command. The Where clause supports DELETE and UPDATE command, which is used to define the removed and updated rows.

The WHERE condition can be used with logical and comparison operators, as shown in the below table:

Different Operators Description
AND Logical operator AND
OR Logical operator OR
= Equal
> Greater than
< Less than
<> or != Not equal
>= Greater than or equal
<= Less than or equal
IN The IN operator will return true if a value matches any value in a list
LIKE The LIKE operator is used to return true if a value matches a pattern
BETWEEN The BETWEEN operator is used to return true if a value is between a range of values
NOT Negate the result of other operators
IS NULL It is used to return true if a value is NULL.

Examples of PostgreSQL WHERE condition

Lets us see some examples where we learn how the WHERE clause works in PostgreSQL.

For this, we are going to take the Employee table, which we created in the earlier section of the PostgreSQL tutorial.

The below screenshot defines the different columns present in the Employee table:

PostgreSQL WHERE Clause

In the above table, we will perform different types of operators using the where clause.

  • Example of AND operator using WHERE clause

In the below example, we will be using the And logical operator for combining the two Boolean expressions in the employee table to find first_name is Mia and last_name is Smith.

SELECT first_name, last_name
FROM 
employee
WHERE 
first_name = 'Mia' AND last_name = 'Smith';

Output

After executing the above command, we will get the below output:

PostgreSQL WHERE Clause
  • Example of the (=) equal operator using WHERE clause

In the below command, we are using the Equal (=) operator in the employee table using the where condition to identify the records whose last_name is equal to smith.

SELECT first_name,last_name
FROM employee
WHERE last_name = 'smith';

Output

After executing the above command, we will get the below output that fetches the two records whose last _name is equal to smith.

PostgreSQL WHERE Clause
  • Example of OR operator using WHERE clause

In the below example, we will be using the OR logical operator in the employee table to identify the first_name as Megan, and last_name as Will.

SELECT first_name, last_name
FROM 
employee
WHERE 
first_name = 'megan' OR last_name = 'will';

Output

After implementing the above command, we will get the below output:

PostgreSQL WHERE Clause
  • Examples of the LIKE operator using the WHERE clause

Here, we are going to use the LIKE operator for identifying a string, which matches a defined design.

The below command displays those records whose last_name starts with the string smi in the employee table.

SELECT first_name,last_name
FROM employee
WHERE last_name LIKE 'smi%';

Output

After executing the above command, we will get the below output, where the above query fetches the four records whose last_name starts with smi string:

PostgreSQL WHERE Clause

Note: In the above command, the % is known as wildcard, which helps to match any string. And the 'smi%' pattern matches those strings which begins with 'smi'.

  • Examples of IN operator using WHERE clause

Here, we will use the IN operator to match a string with any string in a list.

The below command displays those records whose last_name is smi, smith, in the employee table.

SELECT first_name,last_name
FROM employee
WHERE last_name IN ('smi','smith');

Output

Once we implemented the above command, we will get the below output:

PostgreSQL WHERE Clause
  • Example of BETWEEN operator using the WHERE clause

In the below command, we will use Between operator to show the last_name, which ends with the string ith and having 2 to 6 characters in the employee table.

Note: In this, we used the LENGTH() function to get the number of an input string characters.

SELECT last_name, LENGTH(last_name) name_length
FROM employee
WHERE last_name LIKE '%ith' AND LENGTH(last_name) BETWEEN 2 AND 6
ORDER BY name_length;

Output

Once we execute the above command, we will get the below output, where the above query fetches the two records which match the ith string, and the name length is between 2 and 6.

PostgreSQL WHERE Clause
  • Examples of not equal operator (<>) using the WHERE clause

In the below example, we will use the Not equal <> operator to identify those employees whose address begins with New string, and first_name is not equal to Olivia.

SELECT first_name, last_name, address
FROM employee
WHERE address LIKE 'New%' AND first_name <> 'Olivia';

Output

Once we execute the above command, we will get the below output, where the query fetches the two records whose address matches the New string, and the first_name is not equal to Olivia.

PostgreSQL WHERE Clause

Note: We can use both the <> operator and != operator as they are similar as we can see in the below command:

SELECT first_name, last_name, address
FROM employee
WHERE address LIKE 'New%' AND first_name <> 'John';

Output

After executing the above command, we will get the below output, where the query fetches the one records whose address starts with the new string, and the first_name is not equal to John.

PostgreSQL WHERE Clause




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf