Information Management Week 15: SQL Join

Information Management Week 15: SQL Join

• SQL Join statement combines data rows from two or more tables based on a common field.
• The objectives include learning how to use the Join SQL statement, understanding different types of SQL joins, and creating SQL queries that join multiple tables.

Different Types of SQL Joins

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matching records from the right table.
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matching records from the left table.
• FULL OUTER JOIN: Combines results from LEFT and RIGHT joins, returning all rows from both tables. Null values are used for unmatched rows.

Inner Join Syntax

• The simplest type is also known as an equality or equi-join.
• Values in one table must match values in another table.
• Example Syntax:

SELECT table1.column1, table1.column2, table2.column1 FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;

### Display, Search, and Join Columns

- **Display columns:** Appear in the SELECT clause
- **Search Columns:** Appear in the WHERE clause
- **Join columns:** The primary key and foreign key columns used to link tables.
- **Linkage table:**  Contains a join column that links other tables through foreign key values, used in many-to-many relationships.

### Left Join

- Returns all rows from the left table and matches rows from the right table.
- Unmatched rows from the right table are represented with null values.
- Also known as LEFT OUTER JOIN
- Example Syntax:
  ```sql
SELECT table1.column1, table1.column2, table2.column1
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

Right Join

• Returns all rows from the right table and matches rows from the left table.
• Unmatched rows from the left table are represented with null values.
• Also known as RIGHT OUTER JOIN.
• Example Syntax:

SELECT table1.column1, table1.column2, table2.column1 FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;

### Full Join

- Combines results from both LEFT and RIGHT joins, including all rows from both tables.
- Unmatched rows from either table are represented as null values.
- Commonly used to get a comprehensive view of all data from interacting tables which might not have a complete match.
- Example Syntax: 
```sql
SELECT table1.column1, table1.column2, table2.column1
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

This quiz focuses on SQL Joins, essential for combining data from multiple tables in structured queries. You will learn about different types of joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, as well as their syntax and use cases in query formation.