Introduction to SQL Joins: Inner, Outer, Left, Right, and Cross

           

Introduction to SQL Joins:

SQL Joins are used to combine data from multiple tables into a single result set. There are five types of SQL joins: Inner, Outer, Left, Right, and Cross.

1. Inner Join:

An Inner Join returns only the rows that have matching values in both tables being joined.

The syntax of an Inner Join is:

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

Here, table1 and table2 are the tables being joined, column is the column on which the join is based, and column(s) are the columns being selected.

2. Left Join:

A Left Join returns all the rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, then NULL values are returned.

The syntax of a Left Join is:

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

Here, table1 is the left table, table2 is the right table, column is the column on which the join is based, and column(s) are the columns being selected.

3. Right Join:

A Right Join returns all the rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, then NULL values are returned.

The syntax of a Right Join is:

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

            Here, table1 is the left table, table2 is the right table, column is the column on which the join is based, and column(s) are the columns being selected.

4. Full Outer Join:

A Full Outer Join returns all the rows from both tables, along with NULL values where there are no matches.

The syntax of a Full Outer Join is:

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

          Here, table1 is the left table, table2 is the right table, column is the column on which the join is based, and column(s) are the columns being selected.

5. Cross Join:

A Cross Join returns the Cartesian product of the two tables being joined, i.e., all possible combinations of rows from both tables.

The syntax of a Cross Join is:

   SELECT column(s) FROM table1 CROSS JOIN table2;

Here, table1 and table2 are the tables being joined, and column(s) are the columns being selected.

Joins are a powerful tool in SQL, allowing you to combine data from multiple tables into a single result set. Understanding the different types of joins and their syntax is an essential part of working with databases.