WHERE Clause in Postgres

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.

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"
...
...

Tweet about it