Introduction:
In the database world, we often need to store related data in different tables. But what if you want to aggregate all of this data and view it at one location? And this is where SQL JOINS step in. JOINs are for fetching data from the set of tables based on certain related column(s) between tables.
It is very important to understand how JOINs work if you are to be good at writing SQL queries. Whether you’re a newbie or just brushing up on your skills, this guide will help you break down major types of SQL joins.
SQL JOINS
SQL joins are the basic operations performed in SQL queries to combine the multiple tables in a single output table. It uses the common rows present in both tables to combine them. We use Joins to retrieve data and perform operations on multiple tables at a time.
Joins allow to retrieval of data from multiple tables efficiently. It is helpful to solve complex queries. In order to merge two or more tables, we use the join keyword that creates a temporary data table.
Also, we use the join keyword with some clauses that help to fetch the only we want. SQL Joins also help to reduce redundancy and remove unnecessary data. It helps to normalize the data.
In SQL, we used the primary key and foreign key to combine the tables. The primary key is unique and not NULL in the first table. Foreign is also unique in the second table that helps to merge the table.
Syntax of JOIN:
Sure! Here's a rewritten version of your SQL CREATE TABLE statement, using more descriptive constraints and formatting for clarity:
SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;
Parameters
SELECT: This command helps to retrieve the data from the tables.
Columns: These are the columns that we want to show in the result.
JOIN: Join is used to connect rows from tables based on a common column.
ON: In the syntax ON clause is used to specify the joining condition that tells how these tables are connected.
Example
For a better explanation of Join, we used a step-by-step explanation of the examples.
Step1: We create the first table named actors and insert data.
Create Table actors
CREATE TABLE actors (
actor_id INT PRIMARY KEY,
actor_name VARCHAR(100),
movie_id INT
);
Insert Data into actors
INSERT INTO actors (actor_id, actor_name, movie_id) VALUES
(1, 'Vin Diesel', 301),
(2, 'Marilyn Monroe', 302),
(3, 'Alvaro Morte', 303);
Output
Step 2: Create a second table named movies and insert the data.
Create Table movies
CREATE TABLE movies (
movie_id INT PRIMARY KEY,
movie_title VARCHAR(100)
);
Insert Data into movies
INSERT INTO movies (movie_id, movie_title) VALUES
(301, 'Fast and Furious'),
(302, 'Blonde'),
(303, 'Money Heist'),
(304, 'Titanic'),
(305, 'John Wick');
Output
Step 3: Write the query to perform the Join operation to match the movie id.
SELECT actors.actor_name, movies.movie_title
FROM actors
JOIN movies
ON actors.movie_id = movies.movie_id;
Output:
Explanation:
In the above example, we create two tables and insert data into them. In the first table, we have the actor’s name and movie id that we connect to the second table in which we have the movie id and movie_title. With the help of SELECT, we only see the actor_name and movie_title in the output.
We see that movie_id is common in both tables, which helps to join both tables. We see that movie_id is common in both the tables by which we fetch the data into output. With the use of the ON clause, we used the condition to join common columns in tables.
Types of Joins:
- Inner Join
- Left Join
- Right Join
- Full Join
1) Inner Join
Inner Joins are the statements used to fetch the matched records from the tables based on the specific condition. With the help of inner join, we fetched the matching rows present in both tables based on a common column.
It removes duplicates from the tables. We only see the redundant data in the output table.
Syntax:
SELECT *
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Parameters:
FROM table1: With the help of from, we can select the first table to join the second table.
INNER JOIN table 2: INNER JOIN is used to fetch the matching data in both tables.
Example:
For a better understanding, you can see the example.
Table 1: Employee
Table 2: Departments
Query to perform an inner join:
SELECT
employees.emp_id,
employees.name,
departments.department_name
FROM employees
INNER JOIN departments
ON employees.emp_id = departments.emp_id;
Output:
Explanation:
In the above example, with the help of emp_id, we will connect both the tables and show the emp_id, name, and department_name in the output. In this example we see the inner join shows the data after removing the redundancy.
2) Left Join
Left Join is mainly used to fetch all columns and rows left table includes the columns from the right table based on the condition. When the row is present in the left table but it does not exist in the right table then the NULL value is returned. But when the row is present in the right table but not in the left, then it will not show in the result.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Parameters
FROM table1: With the help of this, we select the first table to start the operation.
LEFT JOIN table: LEFT JOIN tells that we perform a left join operation by which we fetch all data from table 1 and match it to table 2.
Example
Table 1: Customers
Table 2: Orders
Query for Left Join:
SELECT Customers.customer_id, Customers.name, Orders.order_id, Orders.product
FROM Customers
LEFT JOIN Orders
ON Customers.customer_id = Orders.customer_id;
Output:
Explanation:
In the above example, Alice and Bob orders are matched. The order details of both are shown in the result. But Charlie has no order, so its order_id and product details are null.
3) Right Join
Right Join is mainly used to fetch all columns and rows from the right table, including the columns from the left table based on the condition. When the row is present in the right table but does not exist in the left table, then the NULL value is returned. But when the row is present in the left table but not in the right, then it will not show in the result.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
Parameters:
FROM table1: It selects the first table to start the join operation.
RIGHT JOIN table 2: It connects the right table to the left to fetch the data from both tables.
Example:
Table 1: Employees
Table 2: Departments
Query to perform Right Join:
SELECT Employees.name, Employees.department_id, Departments.department_name
FROM Employees
RIGHT JOIN Departments
ON Employees.department_id = Departments.department_id;
Output:
Explanation:
In the above example, we fetch the list of all records that meet the condition of the table. Right join is used between the Employee and Department table and fetches the data. In output we see the list of all departments either the department_id and is NULL or NOT NULL.
4) Full Join
It is also known as the full Outer join. It is the combination of LEFT JOIN and RIGHT JOIN. It creates a combination of multiple tables that includes all the null values.
It is used when we want to store the records in a single table with all the values, whether they are matched or not. It also stores the missing data.
Syntax:
SELECT col1, col2…col N
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;
Parameter:
FROM table1: It sets the first table where the query starts for joining it to the second table.
FULL JOIN table 2: With the help of this, we perform the FULL JOIN between table1 and table2.
Example:
For a better understanding of FULL JOIN, we used two tables and performed the operation.
Table 1: Sales
Table 2: Customer
Query to perform FULL JOIN:
SELECT
sales_data.customer_id,
sales_data.sale_amount,
sales_data.sale_date,
customer_feedback.feedback_score,
customer_feedback.feedback_date
FROM
sales_data
FULL JOIN
customer_feedback
ON
sales_data.customer_id = customer_feedback.customer_id;
Output:
Explanation:
In the output, we see that customers 201 and 205 had sales but no feedback. This type of join is very useful in bussines to track the records of customers and sales. It is useful to show a comlete view of data.
Conclusion
In the article, How to Use JOINs in SQL: INNER, LEFT, RIGHT, and FULL; Joins are the operations that allow combining data from multiple tables based on the columns. With the help of joins, we retrieve all matched and unmatched data from the rows. Joins work with relational databases. We can handle the complex queries and handle the data efficiently.
I suggest you learn the SQL from the Tpoint tech website as this website provides SQL tutorials, interview questions, and Online Compiler as well.