Loading...

SQL Joins (INNER, LEFT, RIGHT, FULL)

Welcome to the world of SQL! If you're here, you're likely aware that SQL, or Structured Query Language, is a domain-specific language used in programming for managing data held in a relational database management system (RDBMS). Today, we're going to delve into a crucial aspect of SQL: Joins. SQL joins are used to combine rows from two or more tables based on a related column between them, and they form the backbone of many data operations. It's essential to understand how they work to use them effectively. We'll take a comprehensive look at the four main types of SQL joins: INNER, LEFT, RIGHT, and FULL.

Introduction to SQL Joins

SQL Joins can be considered as the SQL's way of combining data from two or more tables based on a common field between them. It's an essential technique that enables us to create more complex and versatile queries, allowing for a more in-depth analysis of the data. It also helps in reducing the redundancy in the database design, resulting in more efficient data management.

To help you understand SQL Joins, we will use the following two tables in our examples:

Employees table:

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.0
2 Khilan 25 Delhi 1500.0
3 kaushik 23 Kota 2000.0
4 Chaitali 25 Mumbai 6500.0
5 Hardik 27 Bhopal 8500.0
6 Komal 22 MP 4500.0
7 Muffy 24 Indore 10000.0

Orders table:

OID DATE CUSTOMER_ID AMOUNT
102 2023-05-18 3 3000
100 2023-05-17 3 1500
101 2023-05-20 2 1560
103 2023-05-20 4 2060

In the next sections, we will explore how to use different types of joins to bring together data from these two tables.

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It's the most common type of join. You can use it to combine rows from two or more tables if they meet certain criteria.

The basic syntax of INNER JOIN is as follows:

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

Let's say we want to list all orders along with the employees' details who placed them. We can accomplish this using an INNER JOIN:

SELECT Employees.NAME, Employees.AGE, Orders.AMOUNT FROM Employees INNER JOIN Orders ON Employees.ID = Orders.CUSTOMER_ID;

This query will return a table with the names and ages of employees along with the amount of orders they have placed.

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). If there is no match, the result is NULL on the rightside.

The basic syntax of LEFT JOIN is as follows:

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

Let's say we want to list all employees and their respective orders, but also include employees who have not placed any orders. We can achieve this using a LEFT JOIN:

SELECT Employees.NAME, Employees.AGE, Orders.AMOUNT FROM Employees LEFT JOIN Orders ON Employees.ID = Orders.CUSTOMER_ID;

This query will return a table with the names and ages of employees along with the amount of orders they have placed. Employees who have not placed any orders will also be listed, with NULL listed in the AMOUNT field.

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). If there is no match, the result is NULL on the left side.

The basic syntax of RIGHT JOIN is as follows:

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

Consider the case where we want to list all orders and match them with employees' details. If an order doesn't match with an employee, it should still be listed. We can achieve this with a RIGHT JOIN:

SELECT Employees.NAME, Employees.AGE, Orders.AMOUNT FROM Employees RIGHT JOIN Orders ON Employees.ID = Orders.CUSTOMER_ID;

This query will return a table that includes all orders along with details of the employees who placed them. Orders that were not placed by an employee in the employees table will still be listed, with NULL values in the NAME and AGE fields.

FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records. If there is no match, the result is NULL on both sides.

The basic syntax of FULL JOIN is as follows:

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

In our case, if we want to list all employees and their respective orders, but also include employees who have not placed any orders AND orders which are not placed by an employee, we can achieve this using a FULL JOIN:

SELECT Employees.NAME, Employees.AGE, Orders.AMOUNT FROM Employees FULL JOIN Orders ON Employees.ID = Orders.CUSTOMER_ID;

This query will return a table with the names and ages of employees along with the amount of orders they have placed. Employees who have not placed any orders will be listed with NULL in the AMOUNT field. Similarly, orders that are not associated with an employee will also be listed, with NULL in the NAME and AGE fields.

Frequently Asked Questions (FAQ)

1. What is the difference between INNER JOIN and OUTER JOIN?

The main difference between INNER JOIN and OUTER JOIN is that INNER JOIN returns the rows that match in both tables, while OUTER JOIN returns all rows from one table and the matched rows from the other table. If there is no match, the result is NULL on the side of the table that doesn't have a match.

2. How does SQL handle NULL values in JOIN operations?

If one table has a NULL value in the column being joined, SQL will not be able to find a match and will therefore return NULL in the fields of the joined table for that record.

3. What is a Self Join and how is it different from other joins?

A Self Join is aregular join in which a table is joined with itself. SQL doesn't support the concept of joining a table with itself, but you can achieve it using an alias of the table. It can be used when the data related to one row is present in another row of the same table.

4. Is it possible to combine more than two tables using SQL Joins?

Yes, you can join more than two tables in SQL. The same principles of joining two tables apply—you just have to make sure that there's a common column between each table you join.

5. Can we use JOIN with WHERE clause?

Absolutely. The WHERE clause can be used in conjunction with SQL Joins to filter the results based on a condition. It can be used after the JOIN clause to filter the results of the join operation.

6. Is the order of tables in JOIN statement important?

In INNER JOINs, the order does not matter. However, for OUTER JOINs (LEFT, RIGHT, FULL), the order of tables in the JOIN statement can impact the result, because the JOIN type determines which table's non-matching rows are included in the result.

I hope you've found this blog post helpful and now feel more comfortable using SQL Joins. With a solid understanding of INNER, LEFT, RIGHT, and FULL Joins, you should be able to write more complex and versatile SQL queries to better analyze your data.

Remember, practice is key when it comes to mastering SQL. Feel free to play around with these joins in your own databases to get a feel for how they work.

Sharing is caring

Did you like what Mehul Mohan wrote? Thank them for their work by sharing it on social media.

0/10000

No comments so far