SQL на котиках: Джоины (Joins)

Introduction to Join Operators in SQL

In this section, we will discuss join operators in SQL, which are used to select data from two tables and combine them into a single result set based on certain rules.

Joining Tables with Simple Query

• The simplest join query combines two tables based on a common field.

• If the values of the common field are the same in both tables, the rows will be combined into one result.

• We can understand this with an example of joining the "kids" table (with columns for cat names) and the "shops" table (with columns for shop IDs and names).

• To see which cats live in which shops, we need to output the cat name and shop name by joining these tables using a common field like "shop_id".

Handling Missing Values

• In reality, there may be cases where a value in one table does not have a corresponding value in another table.

• To handle such situations, different types of joins are available:

• Inner join: Only returns records that have matches in both tables.

• Left join: Returns all records from the left (first) table and matching records from the right (second) table. For non-matching records, it uses NULL values.

• Right join: Returns all records from the right (second) table and matching records from the left (first) table. For non-matching records, it uses NULL values.

• Full join or outer join: Returns all records when there is a match in either left or right table.

Examples of Different Join Types

• Inner join: Outputs only two rows for our previous example as it only includes matching pairs. Non-matching pairs are excluded.

• Left join: Adds two more rows to include non-matching pairs from the first table with NULL values for missing information from the second table.

• Right join: Adds two more rows to include non-matching pairs from the second table with NULL values for missing information from the first table.

• Full join: Outputs all records from both tables, including non-matching pairs. The final result may have more fields than each individual table.

The timestamps provided in this summary are approximate and should be verified with the actual video.