SQL – Joins

In SQL, a join operation combines rows from two or more tables based on a related column between them. SQL – Joins

There are several types of joins in SQL, including:

  1. INNER JOIN: Returns rows when there is at least one match in both tables.
  2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and any matching rows from the right table. If there is no match, the result will contain NULL values.
  3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and any matching rows from the left table. If there is no match, the result will contain NULL values.
  4. FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables, and any matching rows from both tables. If there is no match, the result will contain NULL values.
  5. CROSS JOIN: Returns the Cartesian product of the two tables, which is the number of rows in the first table multiplied by the number of rows in the second table. This type of join is used less frequently.
  6. SELF JOIN : It is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
  7. NATURAL JOIN: Returns rows when there is at least one match of all the columns with the same name in both tables.

Let’s understand the above concepts with an examples

Now we are creating two tables and performing different types of joins in SQL.

To create tables, you can use the CREATE TABLE statement. Here’s an example of how you might create two tables, “customers” and “orders”:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Now we can perform different types of joins on these two tables:

INNER JOIN :

An INNER JOIN in SQL is used to combine rows from two or more tables based on a related column between them. It returns only the rows where there is a match in both tables. 

SELECT * FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This will return only the rows where there is a match in both tables based on customer_id.

LEFT JOIN:

A LEFT JOIN returns all rows from the left table, and any matching rows from the right table. If there is no match, NULL values are returned for right table’s columns.

SELECT * FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This will return all rows from the left table (customers) and the matching rows from the right table (orders). If there is no match, NULL values will be returned for right table.

RIGHT JOIN:

A RIGHT JOIN returns all rows from the right table, and any matching rows from the left table. If there is no match, NULL values are returned for left table’s columns.

SELECT * FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This will return all rows from the right table (orders) and the matching rows from the left table (customers). If there is no match, NULL values will be returned for left table.

FULL JOIN:

A FULL JOIN returns all rows from both tables, including any duplicates. If there is no match, NULL values are returned for the non-matching table’s columns.

SELECT * FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

This will return all rows from both tables, and if there is no match, NULL values will be returned for non-matching columns

CROSS JOIN:

A CROSS JOIN returns the Cartesian product of the two tables, which is the result of combining each row of the first table with each row of the second table.

SELECT * FROM customers
CROSS JOIN orders;

This will return the Cartesian product of the two tables, which is every possible combination of rows from both tables.

SELF JOIN:

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

SELECT a.first_name, b.first_name
FROM customers a
JOIN customers b
ON a.customer_id <> b.customer_id;

This will join the same table twice, with two different aliases, and returns the combination of each row from the table with every other row from the same table.

NATURAL JOIN:

A NATURAL JOIN compares all columns of the two tables and returns rows that have matching values in all common columns. It is important to note that the column names and data types must be the same in both tables for a natural join to work correctly.

SELECT * FROM customers
NATURAL JOIN orders;

This will join the tables based on all columns with the same name, and returns the combination of the rows with matching values in those columns.

Advantages of JOINS :

Joins in SQL have several advantages:

  1. Data Retrieval: Joins allow you to retrieve data from multiple tables as if they were a single table. This makes it easier to retrieve data that is spread across multiple tables.
  2. Data Consistency: Joins can be used to enforce data consistency by ensuring that related data is retrieved from multiple tables.
  3. Data Integrity: Joins can be used to ensure data integrity by preventing the insertion of inconsistent data into the database.
  4. Data Normalization: Joins can be used to implement data normalization, which is the process of breaking down data into smaller, more manageable tables.
  5. Performance: Joins can improve query performance by reducing the amount of data that needs to be retrieved from the database.
  6. Flexibility: Joins allow you to retrieve data in different ways by combining data from multiple tables, which can be useful in a variety of situations.
  7. Ease of use: SQL provides a simple and straightforward syntax for joining tables, making it relatively easy to retrieve data from multiple tables.
  8. Portability: SQL is a widely-used, standardized language for relational databases, making it easy to transfer SQL code between different databases.

SQL – Joins SQL – Joins SQL – Joins

SQL – Joins SQL – Joins