Joins

Joins:

Joins are used to combine rows from two or more tables based on a related column between them. Here are examples of different types of joins:

INNER JOIN

Syntax:

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Usage:

• Use when you want to retrieve only the rows that have matching values in both tables.
• Ideal for scenarios where you need data from both tables that satisfy a common condition.

Example: INNER JOIN

-- Selecting books along with author information using INNER JOIN
SELECT Books.Title, Authors.FirstName, Authors.LastName
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID;

Explanation:

• This query uses an INNER JOIN to combine rows from the Books table with the corresponding rows from the Authors table based on the matching AuthorID column.
• The result includes the Title of each book along with the FirstName and LastName of the author.

TIPS: Use INNER JOIN:

• When you only want rows that have matching values in both tables.
• Use it in situations where the relationship between tables is mandatory.

LEFT JOIN (or LEFT OUTER JOIN):

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Usage:

• Use when you want to retrieve all rows from the left table and the matching rows from the right table.
• Useful when you want to include all records from the left table, even if there are no matches in the right table.

Example: LEFT JOIN (or LEFT OUTER JOIN)

-- Selecting all authors and their books (including authors with no books) using LEFT JOIN
SELECT Authors.FirstName, Authors.LastName, Books.Title
FROM Authors
LEFT JOIN Books ON Authors.AuthorID = Books.AuthorID;

Explanation:

• This query uses a LEFT JOIN to retrieve all rows from the Authors table and the matching rows from the Books table based on the AuthorID column.
• If an author has no corresponding books, the result will include NULL values for book-related columns (Title).

TIPS: Use LEFT JOIN:

• When you want all rows from the left table and matching rows from the right table.
• Useful when you have a “parent” table and a “child” table, and you want to retrieve information even if there are no matches in the child table.

RIGHT JOIN (or RIGHT OUTER JOIN):

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

Usage:

• Use when you want to retrieve all rows from the right table and the matching rows from the left table.
• Similar to LEFT JOIN but ensures that all records from the right table are included.

-- Selecting all books and their authors (including books with no author) using RIGHT JOIN
SELECT Authors.FirstName, Authors.LastName, Books.Title
FROM Authors
RIGHT JOIN Books ON Authors.AuthorID = Books.AuthorID;

Explanation:

• This query uses a RIGHT JOIN to retrieve all rows from the Books table and the matching rows from the Authors table based on the AuthorID column.
• If a book has no corresponding author, the result will include NULL values for author-related columns (FirstName, LastName).

TIPS: Use RIGHT JOIN:

• Similar to LEFT JOIN, but used when you want all rows from the right table and matching rows from the left table.

FULL JOIN (or FULL OUTER JOIN):

Syntax:

SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

Usage:

• Use when you want to retrieve all rows from both tables, whether or not they have matching values.
• Returns NULL values for columns where there is no match.

EXAMPLE: FULL JOIN

SELECT Customers.CustomerID, Customers.CustomerName, Orders.OrderID, Orders.OrderAmount
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Explanation:

• The FULL JOIN returns all rows when there is a match in either the Customers or Orders table.

TIPS: Use FULL JOIN:

• When you want all rows from both tables, regardless of whether they have matching values.
• Useful when dealing with tables that have partial overlaps.