What are SQL Joins? Types, Syntax and Examples

Explore SQL joins and their power in combining data! Learn about different types, syntax, and real-world examples in this recipe guide.| ProjectPro

Recipe Objective - What are SQL Joins? Types, Syntax and Examples   

SQL is a powerful tool for managing and manipulating data in relational databases. When working with databases, it's often necessary to combine data from multiple tables. SQL Joins provide a way to do this, allowing you to merge data from two or more tables based on a related column or field. This recipe will help you understand and explore what SQL Joins are, the different types of Joins, their syntax, and provide examples to illustrate their usage. 

Understanding SQL Joins: What are Joins in SQL? 

SQL Joins are operations that combine rows from two or more tables based on a related column or key. They allow you to fetch data from multiple tables simultaneously, facilitating the creation of more comprehensive result sets.

How do Joins Work in SQL? 

SQL Joins work by merging data from multiple tables based on related columns. When you perform a Join, the database engine compares the values in the specified columns from the tables and returns a result set containing rows that meet the Join condition. Different Join types (INNER, LEFT, RIGHT, FULL) handle unmatched rows differently, allowing you to control which data is included in the result. Table aliases are often used to distinguish columns with the same name in different tables. Joins are crucial for combining and analyzing data in relational databases, enabling you to generate comprehensive reports and insights from your data.

SQL Joins: Different Types of Joins in SQL

There are several types of SQL Joins, each with its specific characteristics:

We will be taking a look at all of these with the help of examples. We will make use of the customers table and create a new table called orders for all the examples.

Code:

-- creating table orders

CREATE TABLE orders
(order_id NUMERIC PRIMARY KEY,
customer_id NUMERIC,
amount INTEGER);

-- inserting values into orders
INSERT INTO orders VALUES
(1, 101, 78562),
(2,102,14441),
(3,101,2448),
(4,105,56444),
(6,103,4496),
(7,108,46512),
(8,109,4569);

-- retrieving data from orders table
SELECT * FROM orders;

SQL Create Table

Output:

+----------+-------------+--------+

| order_id | customer_id | amount |

+----------+-------------+--------+

|        1 |         101 |  78562 |

|        2 |         102 |  14441 |

|        3 |         101 |   2448 |

|        4 |         105 |  56444 |

|        6 |         103 |   4496 |

|        7 |         108 |  46512 |

|        8 |         109 |   4569 |

+----------+-------------+--------+

7 rows in set (0.00 sec)

We have another table with us - the customers table shown below! 

+-------------+----------------+------+------------+---------+----------+

| customer_id | customer_name  | age  | city       | country | income   |

+-------------+----------------+------+------------+---------+----------+

|         101 | Thomas Shelby  |   30 | Birmingham | England | 90000000 |

|         102 | Grace Burgess  |   28 | Dublin     | Ireland | 10000000 |

|         103 | Alfie Solomons |   40 | London     | England | 78000000 |

|         104 | Michael Gray   |   22 | New York   | USA     | 56000000 |

|         105 | May Carleton   |   29 | Sheffield  | England | 78000000 |

+-------------+----------------+------+------------+---------+----------+

5 rows in set (0.00 sec)

Now that we have two tables ready, let us perform the SQL Join queries.

SQL Joins with Examples 

Let's dive into practical examples to see SQL Joins in action:

INNER JOIN 

In INNER JOIN records that have the same value in both tables are picked.

Syntax:
SELECT column_name1, column_name2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Code:

SELECT orders.order_id, customers.customer_name

FROM orders
INNER JOIN customers 

ON orders.customer_id = customers.customer_id;

SQL Joins Example - INNER JOIN

Output:

+----------+----------------+

| order_id | customer_name  |

+----------+----------------+

|        1 | Thomas Shelby  |

|        2 | Grace Burgess  |

|        3 | Thomas Shelby  |

|        4 | May Carleton   |

|        6 | Alfie Solomons |

+----------+----------------+

5 rows in set (0.00 sec)

LEFT JOIN 

A left join is a type of outer join. All of the rows from the left table are combined with the matching rows from the right table in this join. If there are no matches in the correct table, NULL values are returned.

Syntax:
SELECT column_name1, column_name2, ...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Code:

SELECT orders.order_id, customers.customer_name

FROM orders
LEFT JOIN customers 

ON orders.customer_id = customers.customer_id;

SQL Left Join

 Output:

+----------+----------------+

| order_id | customer_name  |

+----------+----------------+

|        1 | Thomas Shelby  |

|        2 | Grace Burgess  |

|        3 | Thomas Shelby  |

|        4 | May Carleton   |

|        6 | Alfie Solomons |

|        7 | NULL           |

|        8 | NULL           |

+----------+----------------+

7 rows in set (0.00 sec)

Ace Your Data Interviews: Practice SQL with our Projects and prepare for data science and big data job interviews.

RIGHT JOIN 

A Right join is also a type of outer join. All rows from the right table are joined with the matching rows from the left table in this join. If there are no columns that match in the left table, NULL values are returned.

Syntax:
SELECT column_name1, column_name2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Code:

SELECT orders.order_id, customers.customer_name

FROM orders
RIGHT JOIN customers 

ON orders.customer_id = customers.customer_id;

SQL Join query - RIGHT Join

Output:

+----------+----------------+

| order_id | customer_name  |

+----------+----------------+

|        1 | Thomas Shelby  |

|        3 | Thomas Shelby  |

|        2 | Grace Burgess  |

|        6 | Alfie Solomons |

|     NULL | Michael Gray   |

|        4 | May Carleton   |

+----------+----------------+

6 rows in set (0.00 sec)

FULL JOIN 

This is also an outer join. The SQL full join is the outcome of the left and right outer join combined, and the join tables contain all of the records from both tables. It places a NULL in the place of any matches that were not found.

Syntax:
SELECT column_name1, column_name2, ...
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

Or

SELECT column_name1, column_name2, ...
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Code:

SELECT *

FROM customers
FULL OUTER JOIN orders 

ON orders.customer_id = customers.customer_id;

SQL types of joins

Learn Data Processing with Spark SQL using Scala on AWS

SELF JOIN 

A self join is similar to a standard join, except that the table is linked to itself.

Syntax:
SELECT column_name1, column_name2, ...
FROM table_name1, table_name2
WHERE condition;

Code:

SELECT A.customer_name AS CustomerName1, B.customer_name AS CustomerName2, A.city

FROM customers A, customers B
WHERE A.customer_id = B.customer_id
AND A.country = B.country;

SQL Join query - SELF Join

Output:

+----------------+----------------+------------+

| CustomerName1  | CustomerName2  | city       |

+----------------+----------------+------------+

| Alfie Solomons | Thomas Shelby  | London     |

| May Carleton   | Thomas Shelby  | Sheffield  |

| Thomas Shelby  | Alfie Solomons | Birmingham |

| May Carleton   | Alfie Solomons | Sheffield  |

| Thomas Shelby  | May Carleton   | Birmingham |

| Alfie Solomons | May Carleton   | London     |

+----------------+----------------+------------+

6 rows in set (0.00 sec)

SQL MULTIPLE JOINS 

Let's say we have 3 tables – students, marks, and attendance. We need to look at the data from all three tables at a glance. We can do so with the help of joins. Let us see how –

Syntax:
JOIN
tablename1.column_name=table2.column_name
JOIN
tablename2.column_name=table3.column_name

First, we will create the tables and insert values into them as follows –

Code:

--creating table students

CREATE TABLE students (
student_id NUMERIC PRIMARY KEY,
student_name varchar(80),
teacher_id numeric
);
-- creating table attendence and marks
create table marks(student_id numeric, marks int);
create table attendance(student_id numeric, attendance int);

-- inserting values into students table
INSERT INTO students VALUES
(101,"Annie",1),
(102,"Joey",2),
(103,"Sam",3),
(104,"Jammie",2),
(105,"Sean", 3),
(106,"Martha",1),
(107,"Nate",2);

-- inserting values into table marks
insert into marks values(101,95);
insert into marks values(102,85);
insert into marks values(103,80);
insert into marks values(104,65);
insert into marks values(105,75);
insert into marks values(106,85);
insert into marks values(107,90);

-- inseerting values into table attendence
insert into attendance values(101,75);
insert into attendance values(102,65);
insert into attendance values(103,80);
insert into attendance values(104,80);
insert into attendance values(105,73);
insert into attendance values(106,71);
insert into attendance values(107,68);

SQL query to create a table

Now that we have our tables ready, let us perform multiple joins on them –

Code:

select s.student_id, student_name, marks, attendance

from students as s
inner join marks as m
on s.student_id=m.student_id
inner join attendance as a
on m.student_id=a.student_id;

sql join multiple tables

Output:

+------------+--------------+-------+------------+

| student_id | student_name | marks | attendance |

+------------+--------------+-------+------------+

|        101 | Annie        |    95 |         75 |

|        102 | Joey         |    85 |         65 |

|        103 | Sam          |    80 |         80 |

|        104 | Jammie       |    65 |         80 |

|        105 | Sean         |    75 |         73 |

|        106 | Martha       |    85 |         71 |

|        107 | Nate         |    90 |         68 |

+------------+--------------+-------+------------+

7 rows in set (0.00 sec)

Let us see a second example where we perform multiple left joins.

Code:

select s.student_id, student_name, marks, attendance

from students as s
left join marks as m
on s.student_id=m.student_id
left join attendance as a
on m.student_id=a.student_id
where marks>85;

SQL Multiple joins query

Output:

+------------+--------------+-------+------------+

| student_id | student_name | marks | attendance |

+------------+--------------+-------+------------+

|        101 | Annie        |    95 |         75 |

|        107 | Nate         |    90 |         68 |

+------------+--------------+-------+------------+

2 rows in set (0.00 sec)

Explore More About SQL Join Operations with ProjectPro! 

Understanding SQL joins is a fundamental skill for anyone working with relational databases. While this recipe has provided you with a comprehensive overview of SQL join types, syntax, and examples, it's crucial to gain practical experience through real-world projects. ProjectPro offers an extensive repository of over 270+ data science and big data projects, making it the ideal platform to apply your SQL join knowledge in practical scenarios. So, don't just learn about joins – explore their practical applications with ProjectPro to become a proficient data practitioner.

What Users are saying..

profile image

Ed Godalle

Director Data Analytics at EY / EY Tech
linkedin profile url

I am the Director of Data Analytics with over 10+ years of IT experience. I have a background in SQL, Python, and Big Data working with Accenture, IBM, and Infosys. I am looking to enhance my skills... Read More

Relevant Projects

LLM Project to Build and Fine Tune a Large Language Model
In this LLM project for beginners, you will learn to build a knowledge-grounded chatbot using LLM's and learn how to fine tune it.

End-to-End ML Model Monitoring using Airflow and Docker
In this MLOps Project, you will learn to build an end to end pipeline to monitor any changes in the predictive power of model or degradation of data.

Build ARCH and GARCH Models in Time Series using Python
In this Project we will build an ARCH and a GARCH model using Python

End-to-End Snowflake Healthcare Analytics Project on AWS-1
In this Snowflake Healthcare Analytics Project, you will leverage Snowflake on AWS to predict patient length of stay (LOS) in hospitals. The prediction of LOS can help in efficient resource allocation, lower the risk of staff/visitor infections, and improve overall hospital functioning.

Learn How to Build a Logistic Regression Model in PyTorch
In this Machine Learning Project, you will learn how to build a simple logistic regression model in PyTorch for customer churn prediction.

Digit Recognition using CNN for MNIST Dataset in Python
In this deep learning project, you will build a convolutional neural network using MNIST dataset for handwritten digit recognition.

Machine Learning project for Retail Price Optimization
In this machine learning pricing project, we implement a retail price optimization algorithm using regression trees. This is one of the first steps to building a dynamic pricing model.

Loan Eligibility Prediction using Gradient Boosting Classifier
This data science in python project predicts if a loan should be given to an applicant or not. We predict if the customer is eligible for loan based on several factors like credit score and past history.

PyTorch Project to Build a GAN Model on MNIST Dataset
In this deep learning project, you will learn how to build a GAN Model on MNIST Dataset for generating new images of handwritten digits.

Recommender System Machine Learning Project for Beginners-2
Recommender System Machine Learning Project for Beginners Part 2- Learn how to build a recommender system for market basket analysis using association rule mining.