SQL Joins Explained – Inner, Left, Right And Full Joins

WHAT IS SQL?

SQL (Structured Query Language) is a programming language used to manage and manipulate data stored in relational databases. It is a standard language for accessing and manipulating databases and is widely used in various industries for tasks such as data analysis, data modeling, and data management.

BASIC SYNTAX

Here is a brief overview of the basic syntax of SQL:

• SQL statements are case-insensitive, so you can write them in upper case, lower case, or mixed case.
• SQL statements are terminated by a semicolon (;).
• SQL keywords, such as SELECT and FROM, are written in all capital letters.
• SQL identifiers, such as table and column names, are usually written in lowercase, although you can also use mixed case or uppercase if you prefer.
• SQL uses single quotes (‘) to enclose string values.
• SQL uses double quotes (“) to enclose object names, such as table and column names, if they contain spaces or special characters.

INNER JOIN

An inner join, also known as a simple join, is a type of join that returns only rows that satisfy the join condition. It returns the intersection of the two tables, i.e., the rows that appear in both tables.

Here is the syntax for an inner join:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

EXAMPLE

Suppose we have two tables: customers and orders. The customers table contains information about customers, and the orders table contains information about orders placed by customers.

customers
+----+----------+------------+
| id | name     | city       |
+----+----------+------------+
| 1  | John     | New York   |
| 2  | Alice     | Chicago    |
| 3  | Bob      | Los Angeles|
+----+----------+------------+

orders
+----+---------+------------+
| id | amount  | customerid |
+----+---------+------------+
| 1  | 100     | 1          |
| 2  | 50      | 2          |
| 3  | 75      | 2          |
| 4  | 25      | 3          |
+----+---------+------------+

An inner join of the customers and orders tables would return only the rows that have matching customerid values in both tables:

SELECT *
FROM customers
INNER JOIN orders
ON customers.id = orders.customerid;

+----+----------+------------+----+---------+------------+
| id | name     | city       | id | amount  | customerid |
+----+----------+------------+----+---------+------------+
| 1  | John     | New York   | 1  | 100     | 1          |
| 2  | Alice     | Chicago    | 2  | 50      | 2          |
| 2  | Alice     | Chicago    | 3  | 75      | 2          |
| 3  | Bob      | Los Angeles| 4  | 25      | 3          |
+----+----------+------------+----+---------+------------+

RIGHT JOIN

A right join, also known as a right outer join, is similar to a left join, but it returns all rows from the right table, as well as any matching rows from the left table. If there is no match, the left side will contain NULL values.

Here is the syntax for a right join:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

EXAMPLE

A right join of the customers and orders tables would return all rows from the orders table, as well as any matching rows from the customers table:

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.id = orders.customerid;

+----+----------+------------+----+---------+------------+
| id | name     | city       | id | amount  | customerid |
+----+----------+------------+----+---------+------------+
| 1  | John     | New York   | 1  | 100     | 1          |
| 2  | Alice     | Chicago    | 2  | 50      | 2          |
| 2  | Alice     | Chicago    | 3  | 75      | 2          |
| 3  | Bob      | Los Angeles| 4  | 25      | 3          |
+----+----------+------------+----+---------+------------+

FULL JOIN

A full join, also known as a full outer join, is a type of join that returns all rows from both tables, whether or not there is a match. If there is no match, the NULL values will be returned for the missing side.

Here is the syntax for a full join:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

EXAMPLE

A full join of the customers and orders tables would return all rows from both tables, whether or not there is a match:

SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.id = orders.customerid;

+----+----------+------------+----+---------+------------+
| id | name     | city       | id | amount  | customerid |
+----+----------+------------+----+---------+------------+
| 1  | John     | New York   | 1  | 100     | 1          |
| 2  | Alice     | Chicago    | 2  | 50      | 2          |
| 2  | Alice     | Chicago    | 3  | 75      | 2          |
| 3  | Bob      | Los Angeles| 4  | 25      | 3          |
+----+----------+------------+----+---------+------------+

I hope these examples help to clarify the differences between inner, left, right, and full joins.