SQL Joins Explained: Simple Guide for Beginners
Tags: sql, development, database, interview
Published: 2024-07-11 07:55:23

# SQL Joins

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

Let's look at a selection from the "Orders" table:

| OrderID | CustomerID | OrderDate |
| --- | --- | --- |
| 10308 | 2 | 1996-09-18 |
| 10309 | 37 | 1996-09-19 |
| 10310 | 77 | 1996-09-20 |

Then, look at a selection from the "Customers" table:

| CustomerID | CustomerName | ContactName | Country |
| --- | --- | --- | --- |
| 1 | Alfreds Futterkiste | Maria Anders | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mexico |

Notice that the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.

Then, we can create the following SQL statement (that contains an `INNER JOIN`), that selects records that have matching values in both tables:

```sql
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
```

▶[Run](https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join)🔗

---

## Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

* `(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
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1720004718023/c709927a-fc7f-44e8-af04-e369fc0890a4.png align="center")

# SQL INNER JOIN

### INNER JOIN

The `INNER JOIN` keyword selects records that have matching values in both tables.

Let's look at a selection of the [**Products**](https://www.w3schools.com/sql/trysql.asp?filename=trysql_products) table:

| ProductID | ProductName | CategoryID | Price |
| --- | --- | --- | --- |
| 1 | Chais | 1 | 18 |
| 2 | Chang | 1 | 19 |
| 3 | Aniseed Syrup | 2 | 10 |

And a selection of the [**Categories**](https://www.w3schools.com/sql/trysql.asp?filename=trysql_categories) table:

| CategoryID | CategoryName | Description |
| --- | --- | --- |
| 1 | Beverages | Soft drinks, coffees, teas, beers, and ales |
| 2 | Condiments | Sweet and savory sauces, relishes, spreads, and seasonings |
| 3 | Confections | Desserts, candies, and sweet breads |

We will join the Products table with the Categories table, by using the `CategoryID` field from both tables:

Join Products and Categories with the INNER JOIN keyword:

```sql
SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1720005106646/d6accb12-4929-42ed-a33b-021a0eaa769f.png align="center")

### Syntax

`SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;`

## Naming the Columns

It is a good practice to include the table name when specifying columns in the SQL statement.

```sql
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
```

## JOIN or INNER JOIN

`JOIN`*and*`INNER JOIN`*will return the same result*.

`INNER` <mark> is the default join type for </mark> `JOIN`<mark>, so when you write </mark> `JOIN` <mark> the parser actually writes </mark> `INNER JOIN`<mark>.</mark>

```sql
SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID;
```

## JOIN Three Tables

The following SQL statement selects all orders with customer and shipper information:

Here is the [**Shippers**](https://www.w3schools.com/sql/trysql.asp?filename=trysql_shippers) table:

| ShipperID | ShipperName | Phone |
| --- | --- | --- |
| 1 | Speedy Express | (503) 555-9831 |
| 2 | United Package | (503) 555-3199 |
| 3 | Federal Shipping | (503) 555-9931 |

```sql
SELECT * FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShippedID = Shippers.ShipperID);
```

# SQL LEFT JOIN Keyword

The `LEFT JOIN` keyword returns all records from the left table (table1), and the matching records from the right table (table2). <mark>The result is 0 records from the right side, if there is no match.</mark>

### LEFT JOIN Syntax

```sql
Select column_name(s)
from table1
left join table2
on table1.column_name = table2.column_name;
```

**Note:** In some databases LEFT JOIN is called LEFT OUTER JOIN.

![SQL LEFT JOIN](https://www.w3schools.com/sql/img_left_join.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1720008186532/ae84a58e-763e-429e-b921-298f2630ce00.png align="center")

---

## Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |

And a selection from the "Orders" table:

| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
| --- | --- | --- | --- | --- |
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |

---

## SQL LEFT JOIN Example

The following SQL statement will select all customers, and any orders they might have:

```sql
select * 
from customers
left join orders on customers.customerid = orders.customerid
order by customers.customername;
```

**Note:** The `LEFT JOIN` keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders).

# SQL RIGHT JOIN Keyword

The `RIGHT JOIN` keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

### RIGHT JOIN Syntax

```sql
select column_name(s)
from table1
right join table2
on table1.column_name = table2.column_name;
```

**Note:** In some databases `RIGHT JOIN` is called `RIGHT OUTER JOIN`.

![SQL RIGHT JOIN](https://www.w3schools.com/sql/img_right_join.png align="center")

## Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Orders" table:

| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
| --- | --- | --- | --- | --- |
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |

And a selection from the "Employees" table:

| EmployeeID | LastName | FirstName | BirthDate | Photo |
| --- | --- | --- | --- | --- |
| 1 | Davolio | Nancy | 12/8/1968 | EmpID1.pic |
| 2 | Fuller | Andrew | 2/19/1952 | EmpID2.pic |
| 3 | Leverling | Janet | 8/30/1963 | EmpID3.pic |

---

## SQL RIGHT JOIN Example

The following SQL statement will return all employees, and any orders they might have placed:

```sql
select *
from orders
right join employees
on employees.employeeid = orders.employee.id
order by orders.orderid;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1720008562934/6be1ed91-1b0e-4800-9975-fef7591b6828.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1720008666397/adeb2ea7-179c-4629-930e-3ffab2190ce3.png align="center")

**Note:** The `RIGHT JOIN` keyword returns all records from the right table (Employees), even if there are no matches in the left table (Orders).

# SQL FULL OUTER JOIN Keyword

A `FULL OUTER JOIN` is a type of join operation in SQL that combines the results of both a `LEFT JOIN` and a `RIGHT JOIN`. It returns all rows from both tables involved in the join, regardless of whether a match exists between them

**Tip:**`FULL OUTER JOIN` and `FULL JOIN` are the same.

### FULL OUTER JOIN Syntax

```sql
select column_name(s)
from table1
full outer join table2
on table1.column_name = table2.column_name
where condition;
```

![SQL FULL OUTER JOIN](https://www.w3schools.com/sql/img_full_outer_join.png align="center")

## Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

| CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
| 2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
| 3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |

And a selection from the "Orders" table:

| OrderID | CustomerID | EmployeeID | OrderDate | ShipperID |
| --- | --- | --- | --- | --- |
| 10308 | 2 | 7 | 1996-09-18 | 3 |
| 10309 | 37 | 3 | 1996-09-19 | 1 |
| 10310 | 77 | 8 | 1996-09-20 | 2 |

---

## SQL FULL OUTER JOIN Example

```sql
select *
from customers
full outer join orders
on customers.customerid = orders.customerid
order by orders.orderid;
```

A selection from the result set may look like this:

| CustomerName | OrderID |
| --- | --- |
| *Null* | 10309 |
| *Null* | 10310 |
| Alfreds Futterkiste | *Null* |
| Ana Trujillo Emparedados y helados | 10308 |
| Antonio Moreno Taquería | *Null* |

**Note:** The `FULL OUTER JOIN` keyword returns all matching records from both tables whether the other table matches or not. <mark>So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.</mark>

# SQL Self Join

🔗[Self Join Explained Well - Click Here!](https://www.tutorialspoint.com/sql/sql-self-joins.htm)

A self join is a regular join, but the table is joined with itself.

Suppose an organization, while organizing a Christmas party, is choosing a Secret Santa among its employees based on some colors. It is designed to be done by assigning one color to each of its employees and having them pick a color from the pool of various colors. In the end, they will become the Secret Santa of an employee this color is assigned to.

As we can see in the figure below, the information regarding the colors assigned and a color each employee picked is entered into a table. The table is joined to itself using self join over the color columns to match employees with their Secret Santa.

### Self Join Syntax

```sql
SELECT columns
FROM table AS alias1
JOIN table AS alias2 ON alias1.column = alias2.column;
```

![Self Join](https://www.tutorialspoint.com/sql/images/selfjoin_1.jpg align="center")

Example -  
[👩‍💻Env to perform this query](https://www.tutorialspoint.com/online_sql_editor.htm) 🎯

```sql
--Create TB
CREATE TABLE People (
  Color_belongs VARCHAR(50),
    Name VARCHAR(50),
    Color_assigned VARCHAR(50)
);
```

```sql
--Insert data
INSERT INTO People (Color_belongs , Name , Color_assigned ) VALUES
('Blue', 'John', 'Red'),
('Green', 'Alex', 'Blue'),
('Red', 'Simon', 'Green');
```

```sql
-- Self Join query -
SELECT 
    p1.Name AS Person,
    p2.Name AS Secret_Santa
FROM 
    People p1, people p2
where p1.Color_assigned = p2.Color_belongs  ;

-- we can use this query for self join too 
SELECT p1.Name AS Person,p2.Name AS Secret_Santa
FROM People p1
join people p2 on p1.Color_assigned = p2.Color_belongs  ;    
```

〽ALT Syntax -

```sql
SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
```

Note - *Unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause.*

### **Example**

Self Join only requires one table, so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn.

```sql
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);
```

▶[Edit & Run](https://www.tutorialspoint.com/online_sql_editor.htm)🔗

Now, insert values into this table using the INSERT statement as follows

```sql
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
```

Now, let us join this table using the following Self Join query. Our aim is to establish a relationship among the said Customers on the basis of their earnings. We are doing this with the help of the WHERE clause.

```sql
SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME 
as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;
```

### **Output**

The resultant table displayed will list out all the customers that earn lesser than other customers −

| **ID** | **EARNS\_HIGHER** | **EARNS\_LESS** | **LOWER\_SALARY** |
| --- | --- | --- | --- |
| 2 | Ramesh | Khilan | 1500.00 |
| 2 | Kaushik | Khilan | 1500.00 |
| 6 | Chaitali | Komal | 4500.00 |
| 3 | Chaitali | Kaushik | 2000.00 |
| 2 | Chaitali | Khilan | 1500.00 |

## **Self Join with ORDER BY Clause**

After joining a table with itself using self join, the records in the combined table can also be sorted in an order, using the ORDER BY clause.

### **Syntax**

Following is the syntax for it −

```sql
SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
ORDER BY column_name;
```