[SQL] - join WHERE clause and ON clause

Truong Bui

Published on: • Last updated:

sql

TABLE OF CONTENTS

  1. Question
  2. Answer

Question

When we use JOIN in SQL , we can use WHERE clause to filter the rows. But what if we want to filter the rows before joining them?

Here is the two tables

1. im_products table

id
product_name
1
Hat
2
Sock
3
T-Shirt
4
Pan
 
CREATE TABLE im_products
	(
	id           SERIAL PRIMARY KEY,
	product_name VARCHAR(100)
	);

INSERT INTO im_products (product_name)
VALUES ('Hat'),
	     ('Sock'),
       ('T-Shirt'),
       ('Pan');

1. im_orders table

id
product_id
user_name
1
1
Peter
2
3
David
3
2
Lata
 
CREATE TABLE im_orders
	(
	id         SERIAL PRIMARY KEY,
	product_id INTEGER REFERENCES im_products (id),
	user_name  varchar(100)
	);

	INSERT INTO im_orders (product_id, user_name)
	VALUES (1, 'Peter'),
	       (3, 'David'),
	       (2, 'Lata');

Answer

INNER JOIN
Inside WHERE clause
SELECT im_products.product_name, im_orders.user_name
	FROM im_products
	JOIN im_orders on im_products.id = im_orders.product_id
	WHERE user_name = 'Peter'
product_name
user_name
Hat
Peter
Inside ON clause
SELECT im_products.product_name, im_orders.user_name
	FROM im_products
	JOIN im_orders on im_products.id = im_orders.product_id AND user_name = 'Peter'
product_name
user_name
Hat
Peter
INFO:
🚀 Both queries will return the same result
LEFT JOIN
Inside WHERE clause
SELECT im_products.product_name, im_orders.user_name
	FROM im_products
	LEFT JOIN im_orders on im_products.id = im_orders.product_id
	WHERE user_name = 'Peter';
product_name
user_name
Hat
Peter
Inside ON clause
SELECT im_products.product_name, im_orders.user_name
	FROM im_products
	LEFT JOIN im_orders on im_products.id = im_orders.product_id AND user_name = 'Peter';
product_name
user_name
Hat
Peter
Sock
null
T-Shirt
null
Pan
null
INFO:
🚀 The result of the query inside WHERE clause will be different from the query inside ON clause. The query insideWHERE clause will return only the rows that match the condition, while the query inside ON clause will return all rows from the left table and the matched rows from the right table.

Hope you find this article useful. If you have any questions, please let me know in the comment section below. 👇