23 - T-SQL - OUTER JOINS - LEFT e RIGHT - Selecionar dados de várias tabelas - SQL Server

Summary

In this tutorial, Fabio from B Training dives into SQL Outer Joins, specifically focusing on Left Join, Right Join, and Full Join. The video explains the concept of joining multiple tables and returning records even when there's no exact match between them. Fabio demonstrates practical examples using SQL Server 2012, showing how to retrieve data from tables with unmatched records. The video also explores the use of clauses to exclude matches and filter results, offering valuable insights on handling SQL queries with incomplete or missing data relationships.

Takeaways

• 😀 LEFT JOIN returns all rows from the left table, even if there is no matching data in the right table, with `NULL` values for unmatched records.
• 😀 RIGHT JOIN returns all rows from the right table, even if there is no matching data in the left table, with `NULL` values for unmatched records.
• 😀 FULL JOIN returns all records from both tables, filling in `NULL` for missing matches from either side.
• 😀 INNER JOIN only returns rows where there is a match between the two tables.
• 😀 LEFT JOIN is especially useful when you want to retrieve all records from one table, regardless of whether they match data in another table.
• 😀 LEFT JOIN can also be used to filter out records with no matches in the other table by using a `WHERE` clause with `IS NULL`.
• 😀 The syntax for LEFT JOIN, RIGHT JOIN, and FULL JOIN is the same, with only the position of the tables changing.
• 😀 To exclude records with matches, use a LEFT JOIN with a `WHERE` clause where the right table's key is `NULL`.
• 😀 When working with NULL values, always use `IS NULL` in conditions, as `=` does not work with `NULL` in SQL.
• 😀 You can practice JOINs by creating tables with missing data (e.g., `NULL` author IDs) to see how different types of joins return data.

Q & A

What is the primary use of an 'Alter Join' in SQL?

An 'Alter Join' is used when you want to return records from two or more related tables, even if there is no exact match between records in one table and another.

What are the three types of 'Alter Joins' discussed in the video?

The three types of 'Alter Joins' are: Left Join, Right Join, and Full Join.

What does a 'Left Join' do in SQL?

A Left Join returns all records from the left table, even if there is no matching record in the right table. If no match is found, the result for the right table will show 'null'.

How does a 'Right Join' differ from a 'Left Join'?

A Right Join is essentially the opposite of a Left Join. It returns all records from the right table, even if there is no matching record in the left table. If no match is found, the result for the left table will show 'null'.

What is the syntax for using a 'Left Join' in SQL?

The syntax for using a Left Join is: 'SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column'. You can omit 'Outer' from 'Left Outer Join'.

Why is the 'Left Join' useful when working with related tables?

A Left Join is useful because it allows you to display all records from the left table, even if there is no match in the right table. It helps in understanding relationships between tables, such as showing which books don't have authors.

How can you exclude matching records in a 'Left Join'?

To exclude matching records in a Left Join, you can add a 'WHERE' clause with 'IS NULL' to filter out records that have corresponding entries in the right table. For example, 'WHERE table2.column IS NULL'.

What is the key difference between 'Left Join' and 'Full Join'?

A Full Join combines the results of both Left and Right Joins, returning all records when there is a match in either table, or even if there is no match at all. It includes unmatched records from both tables.

What does the term 'Outer' refer to in 'Outer Joins'?

In the context of SQL, 'Outer' refers to an extended version of the join where the unmatched rows from one table are included in the result with null values in the columns from the other table. 'Outer' can be omitted for simplicity, as in 'Left Join'.

What is the purpose of the 'IS NULL' condition in SQL?

The 'IS NULL' condition is used to filter rows where a column contains no value (i.e., it is 'null'). This is important when working with joins to identify records that do not have a match in the other table.