How to perform SQL Joins (inner join, left join, right join, and full join)
Master SQL Joins: Inner, Left, Right and Full Joining Guide
INTRODUCTION
With joins in SQL, relational databases are not much fun because they are actually one of the key tools in fetching data from more than one table within a single query. They really help you combine data having common keys for analysis and extraction of meaning. We are going to take you through the most common SQL joins: inner join, left join, right join, and full join. By the end of this blog, you all will know when and how to use each of them.
1. What is SQL Join?
An SQL Join is an operation that combines rows from two or more tables based on a related column. Related data, to normalize them, are separately stored in relational databases within different tables; sometimes it can be split across more than one table. Joins help bridge those tables and get all the data required in one query.
The primary types of joins in SQL are:
Inner Join
Left Join
Right Join
Full Join
Let's see how the different types of join work with examples.
2. Inner Join
Definition
An inner join returns only rows where there are matches in both tables. It means that only records where there is an equivalent in both tables will be shown in the result set.
Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Example
Let's say you have two tables: Customers and Orders.
Customers Table | CustomerID | Name | |------------|-----------| | 1 | Alice | | 2 | Bob | | 3 | Charlie |
Orders Table OrderID | CustomerID | Product |---------|------------|------------| 101 | 1 | Laptop | 102 | 2 | Phone | 103 | 4 | Headphones |
SELECT Customers.CustomerID, Customers.Name, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Result | CustomerID | Name | Product | |------------|--------|---------| | 1 | Alice | Laptop | | 2 | Bob | Phone |
Only the rows with matching CustomerID values of the Customers and Orders are included in this result. Charlie, who has a CustomerID number of 3, is not found here. There is no customer for Order #103. They are also removed.
A left join, or left outer join, returns all the records from the left table and the matched records from the right table. If there are no matches, the result will contain NULL values for columns from the right table.
Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Using the same Customers and Orders tables:
SELECT Customers.CustomerID, Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Result | CustomerID | Name | Product | |------------|---------|------------| | 1 | Alice | Laptop | | 2 | Bob | Phone | | 3 | Charlie | NULL |
Explanation
The left join returns all the rows in Customers. If no row matches the entry found on Customers in column Products then NULL values will be returned for that column like in Charlie's case.
A right join, or right outer join, is nearly identical to a left join, but it returns all of the records from the right table and the matched records from the left table. If there are no matches, then NULL values are returned for columns from the left table.
Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
Example
Again, using the Customers and Orders tables:
SELECT Customers.CustomerID, Customers.Name, Orders.Product
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Result | CustomerID | Name | Product | |------------|---------|------------| | 1 | Alice | Laptop | | 2 | Bob | Phone | | NULL | NULL | Headphones |
Explanation
The right join returns all rows from the Orders table. As no row in the Customers table matches Order 103, the values of the CustomerID and Name columns are NULL.
5. Full Join
Definition
A full join (or full outer join) returns all records if there is a match in either the left or right table. If no match is found, NULL values are returned for the columns from the table without a match.
Syntax
SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;
Practice Example
Using the same Customers and Orders tables:
SELECT Customers.CustomerID, Customers.Name, Orders.Product
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Outcome | CustomerID | Name | Product | |------------|-------------|-----------| | 1 | Alice | Laptop | | 2 | Bob | Phone | | 3 | Charlie | NULL | | NULL | NULL | Headphones |
Explanation
All rows of both tables will be included in a full join. The row that does not have a match is filled with NULL values in the columns of the other table.
LEFT JOIN: Gets every row from the left table irrespective of the match in the right table.
RIGHT JOIN: Obtain every row from the right table irrespective of the match in the left table.
FULL JOIN: Used to obtain every row from both tables where those values do not match.
Conclusion
SQL joins are the very fundamental things one needs to be aware of when working on the database. Each join is for a specific usage; hence, you have to apply them according to your query requirements. Practice of all these joins allows effective retrieval of data sets from relational databases. You're now ready to progress onto other topics related to joining such as self joining and cross joining to develop the skills in SQL further.
Happy querying!
More Details for programming knowledge link :
Best offer on laptop : https://www.howtotech.in/2024/11/top-10-gaming-and-programming-laptops.html?m=1
Summary
