SQL Joins

SQL Joins

Introduction

In SQL, joins are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data from multiple tables in a single query, providing a way to establish relationships and perform more complex data analysis. This documentation will cover the different types of joins in SQL and provide code examples for each type.

Types of Joins

1. Inner Join:

   • Syntax:

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

   • Explanation:
     • The inner join returns only the matching rows from both tables based on the specified condition. It combines the rows where the join condition is true.
     • The table1 and table2 are the names of the tables you want to join.
     • column_name is the column that is used to establish the relationship between the tables.

2. Left Join:

   • Syntax:

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

   • Explanation:
     • The left join returns all the rows from the left table (table1) and the matching rows from the right table (table2). If there is no match, it returns NULL values for the right table columns.
     • The result will contain all the rows from table1 and the matching rows from table2.

3. Right Join:

   • Syntax:

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

   • Explanation:
     • The right join returns all the rows from the right table (table2) and the matching rows from the left table (table1). If there is no match, it returns NULL values for the left table columns.
     • The result will contain all the rows from table2 and the matching rows from table1.

4. Full Outer Join:

   • Syntax:

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

   • Explanation:
     • The full outer join returns all the rows from both tables, matching rows and non-matching rows. If there is no match, it returns NULL values for the columns of the table without a match.
     • The result will contain all the rows from table1 and table2.

Conclusion

SQL joins are powerful tools for combining data from multiple tables. They allow you to retrieve specific information by establishing relationships between tables based on related columns. By understanding the different types of joins and their syntax, you can effectively retrieve and analyze data from complex database structures.