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_expressionIn 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.
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:
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:
- 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.
- 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:
- 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:
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:
- 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.
- 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.
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.
Leave a Reply