SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Different Types of SQL JOINs

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
• SELF JOIN: A self join is a regular join, but the table is joined with itself.

I am using two tables: “users” and “address” in this context.

INNER JOIN

SELECT u.user_id, u.user_name, u.user_phone, a.city, a.pin
FROM users AS u
INNER JOIN address AS a ON a.user_id_fk = u.user_id

LEFT (OUTER) JOIN

LEFT OUTER JOIN and LEFT JOIN are the same.

SELECT u.user_id, u.user_name, u.user_phone, a.city, a.pin
FROM users AS u
LEFT JOIN address AS a ON a.user_id_fk = u.user_id

RIGHT (OUTER) JOIN

RIGHT OUTER JOIN and RIGHT JOIN are the same.

SELECT u.user_id, u.user_name, u.user_phone, a.city, a.pin
FROM users AS u
RIGHT JOIN address AS a ON a.user_id_fk = u.user_id

FULL (OUTER) JOIN

FULL OUTER JOIN and FULL JOIN are the same.

SELECT u.user_id, u.user_name, u.user_phone, a.city, a.pin
FROM users AS u
FULL JOIN address AS a ON a.user_id_fk = u.user_id

SELF JOIN

SELECT a1.city, a1.pin as pin1, a2.pin AS pin2
FROM address AS a1, address AS a2
WHERE a1.pin <> a2.pin
AND a1.city = a2.city