WHERE condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output.
General form:
WHERE condition
Syntax:
SELECT column1, column2, ...
FROM table
WHERE condition;
Just like SQL, WHERE clause comes right after the FROM clause of the SELECT statement.
We can use comparison or logical operators. like >, <, =, LIKE, NOT, etc in the WHERE condition. The condition must evaluate to true, false, or unknown. It can be a Boolean expression or a combination of Boolean expressions using AND and OR operators.
Operator | Name |
---|---|
= | Equal |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
<> or != | Not equal |
AND | Logical operator AND |
OR | Logical operator OR |
I am using the database available on my public Github repo
Simple WHERE example:
SELECT customer_id, contact_name FROM customers WHERE country= 'USA';
//Output
customer_id. contact_name
"GREAL" "Howard Snyder"
"HUNGC" "Yoshi Latimer"
"LAZYK" "John Steel"
"LETSS" "Jaime Yorres"
.....
.....
Example with OR operator:
SELECT customer_id, contact_name FROM customers WHERE country= 'USA' OR country='France';
//Output
customer_id. contact_name
"BLONP" "Frédérique Citeaux"
"BONAP" "Laurence Lebihan"
"DUMON" "Janine Labrune"
"FOLIG" "Martine Rancé"
...
...
Above Example with IN operator, same output:
SELECT customer_id, contact_name FROM customers WHERE country IN('USA', 'France');
//Output
customer_id. contact_name
"BLONP" "Frédérique Citeaux"
"BONAP" "Laurence Lebihan"
"DUMON" "Janine Labrune"
"FOLIG" "Martine Rancé"
...
...
Example with LIKE operator:
SELECT customer_id, contact_name FROM customers WHERE country LIKE 'US%';
//Output
customer_id. contact_name
"GREAL" "Howard Snyder"
"HUNGC" "Yoshi Latimer"
"LAZYK" "John Steel"
"LETSS" "Jaime Yorres"
...
...
Example with BETWEEN operator:
SELECT customer_id, contact_name FROM customers WHERE LENGTH(country) BETWEEN 2 AND 5;
//Output
customer_id. contact_name
"AROUT" "Thomas Hardy"
"BOLID" "Martín Sommer"
"BSBEV" "Victoria Ashworth"
"CONSH" "Elizabeth Brown"
...
...
In the above example, I am just asking Postgres for customer_id and customer_name from the countries that has country_name length BETWEEN 2 and 5.
Example with not equal operator (<>):
SELECT customer_id, contact_name FROM customers WHERE country = 'USA' AND region <> 'SP';
//Output
customer_id. contact_name. region
"GREAL" "Howard Snyder" "OR"
"HUNGC" "Yoshi Latimer" "OR"
"LAZYK" "John Steel" "WA"
"LETSS" "Jaime Yorres" "CA"
...
...
database sql clause sql query WHERE