Yogesh Chauhan's Blog

WHERE Clause in Postgres

in Postgres on May 1, 2020

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.

List of Comparison 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"
...
...

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Explanation of PostgreSQL PgAdmin interfacePostgresWhat is iFrame in HTML? Why do we need it?HTML@forward modules with a prefix in SCSS (Sass)SCSSHow to create a simple stopwatch using JavaScript?JavaScriptPagination in CSS with multiple examplesCSSKilling A Project Part 1: What criteria should be used to cancel/kill a project?Miscellaneous