Осмысляем работу джойнов в sql: от реляционной алгебры до наглядных картинок

LEFT OUTER JOIN

It returns all the rows that satisfy the join condition and also returns all the rows from the table on the left side of the JOIN keyword. Note, if no matching rows are found, it will return a NULL value

SQL JOIN METHOD

In SQL join method, we simply use LEFT OUTER JOIN keyword to join the tables

SELECT m.member_name, b.category
FROM members m LEFT OUTER JOIN books b
ON m.book_id=b.book_id;

OR

SELECT m.member_name, b.category
FROM members m LEFT OUTER JOIN books b
USING (book_id);

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish

Observe that we got all the rows as output from the MEMBERS table because its on the left side of the JOIN keyword. We also have output from the BOOKS table (which is on the right side of the JOIN keyword).

In the above output all records from MEMBERS table are present in the output as all satisfy the join condition. As we do not have any record in the MEMBERS table that do not satisfy the JOIN condition, hence no null values.

TRADITIONAL METHOD

In traditional method of joining, we use “(+)” on the side of table B. Many people get confused abut using “(+)” on the side of table A as it is a LEFT OUTER JOIN

SELECT m.member_name, b.category
FROM members m, books b
WHERE m.book_id = b.book_id (+);

MEMBER_NAME    CATEGORY
-------------- ----------
John           Inspire
Max            Novel
Sam            Business
Leo            Sales
Kish

MySQL CROSS JOIN clause

Unlike the inner join, left join, and right join, the cross join clause does not have a join condition.

The cross join makes a Cartesian product of rows from the joined tables. The cross join combines each row from the first table with every row from the right table to make the result set.

Suppose the first table has rows and the second table has rows. The cross join that joins the tables will return rows.

The following shows the syntax of the cross join clause:

This example uses the cross join clause to join the with the tables:

The cross join is useful for generating planning data. For example, you can carry the sales planning by using the cross join of customers, products, and years.

In this tutorial, you have learned various MySQL join statements, including cross join, inner join, left join, and right join, to query data from two tables.

SQL RIGHT OUTER JOIN JOIN

Другой тип соединения называется SQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны (выполняется условие соединения).

Синтаксис

Синтаксис для RIGHT OUTER JOIN в SQL:

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

В некоторых базах данных ключевое слово OUTER опущено и записывается просто как RIGHT JOIN.

Рисунок

На этом рисунке SQL RIGHT OUTER JOIN возвращает затененную область:

SQL RIGHT OUTER JOIN возвращает все записи из table2 и только те записи из table1, которые пересекаются с table2.

Пример

Теперь давайте рассмотрим пример, который показывает, как использовать RIGHT OUTER JOIN в операторе SELECT.

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

И таблицу orders со следующими данными:

order_id	customer_id	order_date
1	7000	2019/06/18
2	5000	2019/06/18
3	8000	2019/06/19
4	4000	2019/06/20
5	NULL	2019/07/01

Введите следующий SQL оператор:

PgSQL

SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
RIGHT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

1 2 3 4 5 6 7

SELECTcustomers.customer_id, orders.order_id, orders.order_date FROMcustomers RIGHT OUTER JOINorders ONcustomers.customer_id=orders.customer_id ORDERBYcustomers.customer_id;

Будет выбрано 5 записей. Вот результаты, которые вы должны получить:

customer_id	order_id	order_date
NULL	5	2019/07/01
4000	4	2019/06/20
5000	2	2019/06/18
7000	1	2019/06/18
8000	3	2019/06/19

Этот пример RIGHT OUTER JOIN вернул бы все строки из таблицы orders и только те строки из таблицы customers, где объединенные поля равны.

Если значение customer_id в таблице orders не существует в таблице customers, то все поля в таблице customers будут отображаться как NULL в наборе результатов. Как видите, строка, где order_id равен 5, будет включена в RIGHT OUTER JOIN, но в поле customer_id отображается NULL.

4 Разница между INNER / LEFT / RIGHT / FULL JOIN

• INNER JOIN…ON…: Вернуть все строки, которые соответствуют друг другу в левой и правой таблицах (потому что выше выполняется только второй шаг фильтрации ON, а третий шаг не выполняется для добавления внешних строк)
• LEFT JOIN…ON…: Вернуть все строки левой таблицы, если некоторые строки не имеют соответствующих совпадающих строк в правой таблице, установите для столбцов правой таблицы значение NULL в новой таблице
• RIGHT JOIN…ON…: Вернуть все строки правой таблицы, если некоторые строки не имеют соответствующих совпадающих строк в левой таблице, установите для столбцов левой таблицы значение NULL в новой таблице

INNER JOIN

Сделайте третий шаг вышеДобавить внешнюю строкуНапример, еслиLEFT JOINЗаменитьINNER JOIN, Этот шаг будет пропущен, а сгенерированная таблица vt3 будет точно такой же, как vt2:

RIGHT JOIN

ЕслиLEFT JOINЗаменитьRIGHT JOIN, Сгенерированная таблица vt3 выглядит следующим образом:

Поскольку в user_account (правая таблица) есть строка с идентификатором пользователя = 1009, но в user_info (левая таблица) запись для этой строки не найдена, на третьем шаге будет вставлена ​​следующая строка:

FULL JOIN

Указанная выше статьяУпоминается в стандартном определении SQLFULL JOIN, Это не поддерживается в mysql, но мы можем передатьLEFT JOIN + UNION + RIGHT JOIN чтобы понятьFULL JOIN：

Он вернет следующие результаты:

ps: Фактически, мы можем видеть из семантикиLEFT JOINс участиемRIGHT JOINНет никакой разницы. Разница между двумя результатами зависит от порядка размещения левой и правой таблиц. Следующее взято из официальной документации MySQL:

Поэтому, когда вы запутались в использовании LEFT JOIN или RIGHT JOIN, используйте LEFT JOIN как можно чаще.

Conclusion

The SQL CROSS JOIN allows you to generate a Cartesian product for two given sets of data. When the underlying use case calls for generating a Cartesian product like it was the case for our poker game, then using a CROSS JOIN is the idiomatic way for addressing this task.

Note that a Cartesian product could be generated unintentionally, too, in which case it would indicate a flaw in the join conditions. For instance, this could happen when joining two or more unrelated one-to-many table relationships. For more details about this unintentional Cartesian product issues and how you can fix them, check out this article.

Follow @vlad_mihalcea

SQL INNER JOIN | EXAMPLE 1

Let us see example 1 to join the table of Students with Courses with INNER Join Class and with only JOIN clause. We will observe the output result table whether the result of the INNER JOIN and only the JOIN keyword are the same. As it has been mentioned that you can use either INNER JOIN or only JOIN.

Syntax / Code Comparison

• is the Table1
• is the Table2
• is the join_predicate
• SELECT, FROM, INNER JOIN, and ON are the keyword of SQL

OUTPUT
Table 1

Observe the output of the first INNER JOIN Example shown by Table 1, You can see only the four rows are present in the output. You can see above the Students Table that it has five students. The Courses Table has a total of four records. In the output, you can see that only those records are taken which have similar values in the ID field of the Students Table and the SID field of the Courses Table. INNER JOIN defines. Here is the analysis of the rows from both tables.

• ON clause of this SELECT query states that Students.ID must be equal to filed.
• INNER JOIN clause that this will find only match values in both Students.ID and must be equal to field.
• Stephen, Lucas, and Brendon are those students from the Students Table who have matched values in the Courses table.
• Computing, Data structure, programming, and OOP are the courses to which Stephen, Lucas, and Brendon have registered.
• All other 2 students from the Students table do not have any match in the SID column of the Courses table.

SQL INNER JOIN Examples

Suppose we have a demo Database with four tables.

1. The students table is used to store the records about the students.
2. The courses table is used to store the information about the courses.
3. A teacher table is used to store the information about teachers.
4. The CourseRegistration table is used to store the information about the students and their registered courses with teachers.

The following diagram (Figure 2) shows the Tables structure and relationship among these. You can observe that the Students, the Courses, and the Teacher all these tables have one-to-many with CourseRegistration.

Figure -2

The Students Table

ID	StName	Age	City	Fee
1	Stephen	23	London	2000
2	Lucas	32	New York	2100
3	Brendon	34	Oslo	2300
4	Ahmad	31	Mumbai	3400
5	Joe	45	China	2300

The Courses Table

CID	CourseName	Duration	SID	CR
101	Computing	3 months	1	3
201	Programming	4 months	2	3
301	Data Structures	6 Months	1	3
401	OOP	2 Months	3	2

The Teacher Table

TeacherID	TeacherName	TeacherOffice	TeacherGrade
T101	Habib Ullah Qamar	Admin block	17
T102	Deepak Prasad	CS Block	18
T103	Martin	Sociology Block	17
T104	Pressman	SE Block	19
T105	Michael Sisper	CS Block	19
T106	Ahmad	Psychology Block	18

The CourseRegistration Table

Advertisement

TeacherID	StudentID	CourseID	Semester
T101	1	101	Fall
T101	2	201	Fall
T102	3	101	Spring
T103	1	301	Spring
T103	4	301	Fall
T104	5	401	Fall
T105	6	101	Spring
T106	2	201	Fall
T105	3	301	Fall
T106	5	401	Spring

INNER JOIN in Sql Server

Inner Join returns only the matching rows in both the tables (i.e. returns only those rows for which the join condition satisfies).

Demo 1: As per the data in our demo tables, Customers with CustomerId 1 and 3 in Customers table have the orders in the Orders table. Where as the customer with CustomerId 2 doesn’t have any order in the Orders table. So the Inner Join on the CustomerId column between Customers and Orders table will return the Customer and Order details of the Customers with CustomerId 1 and 3 only.

SELECT * 
FROM  Customers C 
		INNER JOIN Orders O
			ON O.CustomerId = C.CustomerId

RESULT:

Demo 2: Below Inner Join query demonstrates how to get name of all the  Customer who have at-least one order in the Orders table.

SELECT C.Name 
FROM  Customers C 
		INNER JOIN Orders O
			ON O.CustomerId = C.CustomerId

RESULT:
Name
——————-
Basavaraj
Shree

SQL INNER JOIN (простое соединение)

Скорее всего, вы уже писали SQL запрос, который использует SQL INNER JOIN. Это наиболее распространенный тип соединения SQL. INNER JOIN возвращает все строки из нескольких таблиц, где выполняется условие соединения.

Синтаксис INNER JOIN в SQL:

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

Рисунок.

На этом рисунке SQL INNER JOIN возвращает затененную область:
SQL INNER JOIN будет возвращать записи, где пересекаются table1 и table2.

Пример

Давайте рассмотрим пример использования INNER JOIN в запросе.

В этом примере у нас есть таблица customer и следующими данными:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

И таблица orders со следующими данными:

order_id	customer_id	order_date
1	7000	2019/06/18
2	5000	2019/06/18
3	8000	2019/06/19
4	4000	2019/06/20
5	NULL	2019/07/01

Выполним следующий SQL оператор:

PgSQL

SELECT customers.customer_id,
orders.order_id,
orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

1 2 3 4 5 6 7

SELECTcustomers.customer_id, orders.order_id, orders.order_date FROMcustomers INNER JOINorders ONcustomers.customer_id=orders.customer_id ORDERBYcustomers.customer_id;

Будет выбрано 4 записи. Вот результаты, которые вы должны получить:

customer_id	order_id	order_date
4000	4	2019/06/20
5000	2	2019/06/18
7000	1	2019/06/18
8000	3	2019/06/19

В этом примере будут возвращены все строки из таблиц customers и orders, в которых совпадают значения поля customer_id в обоих таблицах.

Строки, где значение customer_id равен 6000 и 9000 в таблице customers, будут опущены, поскольку они не существуют в обеих таблицах. Строка, в которой значение order_id равно 5 из таблицы orders, будет опущена, поскольку customer_id со значением NULL не существует в таблице customers.

Вместо заключения

Помните о порядке выполнения соединений и порядке таблиц, если используете несколько соединений и используете внешние соединения. Можно выполнять LEFT JOIN для сохранения всех строк из самой первой таблицы, а последним внутренним соединением потерять часть данных. На маленьких таблицах косяк заметить легко, на огромных очень тяжело, поэтому будьте внимательны.Рассмотрим последний пример и введем еще одну таблицу «Банки», в которой обслуживаются наши придуманные сотрудники:

id	Наименование
1	Банк №1
2	Лучший банк
3	Банк Лидер

В таблицу «Сотрудники» добавим столбец «Банк»:

id	Имя	Отдел	Банк
1	Юлия	1	2
2	Федор	2	2
3	Алексей	NULL	3
4	Светлана	2	4

Теперь выполним такой запрос:

SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел,
  Банки.Наименование AS Банк
FROM
  Сотрудники

  LEFT JOIN Отделы
    ON Сотрудники.Отдел = Отделы.id

  INNER JOIN Банки
    ON Сотрудники.Банк = Банки.id

В результате потеряли информацию о Светлане, т.к. для нее не нашлось банка с id = 4 (такое происходит из-за неправильной проектировки БД):

id	Имя	Отдел	Банк
1	Юлия	Кухня	Лучший банк
2	Федор	Бар	Лучший банк
3	Алексей	NULL	Банк Лидер

Хочу обратить внимание на то, что любое сравнение с неизвестным значением никогда не будет истинным (даже NULL = NULL). Эту грубую ошибку часто допускают начинающие специалисты

Подробнее читайте в статье про значение NULL в SQL.

Пройдите мой тест на знание основ SQL. В нем есть задания на соединения таблиц, которые помогут закрепить материал.

Дополнить Ваше понимание соединений в SQL могут схемы, изображенные с помощью кругов Эйлера. В интернете много примеров в виде картинок.

Привожу простыню запросов, чтобы Вы могли попрактиковаться на легких примерах, рассмотренных в статье:

-- Создаем CTE для таблиц из примеров
WITH Сотрудники AS(
  SELECT 1 AS id, 'Юлия' AS Имя, 1 AS Отдел, 2 AS Банк
  UNION ALL
  SELECT 2, 'Федор', 2, 2
  UNION ALL
  SELECT 3, 'Алексей', NULL, 3
  UNION ALL
  SELECT 4, 'Светлана', 2, 4
),
Отделы AS(
  SELECT 1 AS id, 'Кухня' AS Наименование
  UNION ALL
  SELECT 2, 'Бар'
  UNION ALL
  SELECT 3, 'Администрация'
),
Банки AS(
  SELECT 1 AS id, 'Банк №1' AS Наименование
  UNION ALL
  SELECT 2, 'Лучший банк'
  UNION ALL
  SELECT 3, 'Банк Лидер'
)

-- Если надо выполнить другие запросы, то сначала закоментируй это запрос с помощью /**/,
-- а нужный запрос расскоментируй или напиши свой.
-- Это пример внутреннего соединения
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел
FROM
  Сотрудники
  JOIN Отделы
    ON Сотрудники.Отдел = Отделы.id

/*
-- Пример левого джойна
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел
FROM
  Сотрудники
  LEFT JOIN Отделы
    ON Сотрудники.Отдел = Отделы.id
*/

/*
-- Результат этого запроса будет аналогичен результату запроса выше, хотя соединение отличается
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел
FROM
  Отделы
  RIGHT JOIN Сотрудники
    ON Сотрудники.Отдел = Отделы.id
*/

/*
-- Правое соединение
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел
FROM
  Сотрудники
  RIGHT JOIN Отделы
    ON Сотрудники.Отдел = Отделы.id
*/

/*
-- Пример с использованием разных видов JOIN
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел
FROM
  Отделы
  RIGHT JOIN Сотрудники
    ON Сотрудники.Отдел = Отделы.id
  LEFT JOIN Банки
    ON Банки.id = Сотрудники.Банк
*/

/*
-- Полное внешние соединение
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел
FROM
  Сотрудники
  FULL JOIN Отделы
    ON Сотрудники.Отдел = Отделы.id
*/

/*
-- Пример с потерей строки из-за последнего внутреннего соединения
SELECT
  Сотрудники.id,
  Сотрудники.Имя,
  Отделы.Наименование AS Отдел,
  Банки.Наименование AS Банк
FROM
  Сотрудники

  LEFT JOIN Отделы
    ON Сотрудники.Отдел = Отделы.id

  INNER JOIN Банки
    ON Сотрудники.Банк = Банки.id
*/

/*
-- Запрос с условием, которое всегда будет True
SELECT *
FROM
  Сотрудники
  JOIN Отделы
    ON 1=1
*/

• < Назад
• Вперёд >

Новые статьи:

• Объединение таблиц – UNION

• Соединение таблиц – операция JOIN и ее виды

• Тест на знание основ SQL

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Joining More than Two Tables

There may be times when you need to combine data from more than just two tables. You can join any number of tables together by embedding clauses within other clauses. The following syntax is an example of how this can look when joining three tables:

This example syntax’s clause starts by joining with . After this joining’s clause, it starts a second that combines the initial set of joined tables with . Note that the third table can be joined to a column in either the first or second table.

To illustrate, imagine that you want to know how much revenue your employee’s sales have brought in, but you only care about sales records that involve an employee selling the product they specialize in.

To get this information, you could run the following query. This query starts by joining the and tables together by matching their respective columns. It then joins the table to the first two by matching each row in the initial to its column. The query then filters the results with a clause to only return rows where the matched employee was also the person who made the sale. This query also includes an clause that sorts the final results in ascending order based on the value in the column:

Note that among the columns listed in this query’s clause is an expression that multiplies the values in the table’s column by the table’s values. It returns the products of these values in the matched rows:

All the examples so far have featured the same type of clause: the . For an overview of joins, joins, and how they differ, continue reading the next section.

JOIN Condition

Tables are joined together by at least one common attribute. This common attribute is often referred to as a foreign key. As demonstrated in the excel example, VLOOKUP function also takes advantage of this shared attribute. We use this shared attribute (foreign key) as the matching point where we can find corresponding information of each row in another table. This common attribute needs to be explicitly indicated as the join condition in the SQL statement.

Country Code example (image by author)

Let’s continue with this country code example. The aim of this exercise is to find the country name of each criterion in the “Google_Ads_GeoTargets” table. The datasets are from Google Public Dataset. Have an exploration and try to implement it in the BigQuery if you are interested.

In the left table, we have already got the country_code for each criterion and the Country_Code table provides us with the country name of fips_code. Therefore, the logic is to match the country_code in the GeoTarget table to the fips_code in the Country_Code table and find the corresponding name. country_code and fips_code are the common attributes that build the relationship between two tables.

We write down the following statement to indicate the join condition. Notice that it is better to use the table name as the attribute prefix and don’t forget the keyword “ON”:

ON  Google_Ads_GeoTargets.country_code = Country_Code.fips_code

Mechanics of SQL Joins

When broken down the mechanics of a SQL join are pretty straightforward.  To perform a join you need two items: two tables and a join condition.  The tables contain the rows to combine, and the join condition the instructions to match rows together.

Take a look at the following Venn diagram.  The circles represent the tables and where they overlap rows satisfying the join condition.

You may be wondering what makes up a join condition.  In many cases a join condition is just matching one or more fields in one table to those in another.

A common join pattern used to join the primary key of one table to its foreign key.  I suppose this is self-evident, but I think it is important to highlight it.  Can you think why this is the case?

Use of ORDER BY Clause with INNER JOIN

It has been mentioned in every example and in every output, the order of the output rows is taken from the order of Table1 in the SELECT Query. You can use the ORDER BY clause in INNER JOIN as well as WHERE Clause if you want to change the order of the output rows. ORDER By clause can use any of the Column names either from Table 1 or Table 2 in the following way.

This example will generate the same output as Table 4 but CourseID will be ordered in ascending order. The required SELECT statement expressing How to use the ORDER BY clause with SQL INNER JOIN will be as under :

OUTPUT

Table 6

This time output is the same as the output of Table 5, but the order of the rows is different. CID Columns is in Ascending order with the use of the ORDER BY clause. As far as ORDER is concerned.

Advertisement

Oracle full outer join

Oracle full outer join or full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. If there is no match, the missing side will have nulls.

The following example shows the full outer join of the left and right tables:

The following picture illustrates the result set of the full outer join:

Note that the keyword is optional.

The following Venn diagram illustrates the full outer join:

To get a set of rows that are unique from the left and right tales, you perform the same full join and then exclude the rows that you don’t want from both sides using a clause as follows:

Here is the result:

The following Venn diagram illustrates the above operation:

In this tutorial, you have learned how to use various kinds of Oracle joins to query data from two tables.

Setup tables

For our join example, we will be using sample tables. Create below tables and insert rows before proceeding with Oracle join types.

Create first table Books

CREATE TABLE books (
  book_id   NUMBER(4) CONSTRAINT books_pk PRIMARY KEY,
  title VARCHAR2(30),
  category VARCHAR2(10)
);

Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (1,'Wolf of wallsteet','Business');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (2,'Start with why','Inspire');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (3,'The one thing','Inspire');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (4,'Real Steel','Business');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (5,'Safe house','Novel');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (6,'Leader eat last','Business');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (7,'10X rule','Sales');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (8,'Sell or be sold','Sales');
Insert into BOOKS (BOOK_ID,TITLE,CATEGORY) values (9,'Obama',NULL);

Create second table Members

CREATE TABLE members (
  member_id   NUMBER(2) CONSTRAINT member_pk PRIMARY KEY,
  member_name VARCHAR2(14),
  book_id NUMBER(4) CONSTRAINT members_books_id_fk REFERENCES books(book_id)
);

Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (11,'John',3);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (22,'Max',5);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (33,'Sam',6);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (44,'Leo',7);
Insert into MEMBERS (MEMBER_ID,MEMBER_NAME,BOOK_ID) values (55,'Kish',9);

These tables are for demonstration purpose and you create create it in any database schema.

Db2 Left Join

The left join selects data starting from the left table and matches rows in the right table. Similar to the inner join, the left join returns all rows from the left table and the matching rows from the right table. In addition, if a row in the left table does not have a matching row in the right table, the columns of the right table will have nulls.

Note that the left join is also called the left outer join. The outer keyword is optional.

The following statement joins the table with the table using left join:

Here is the result set:

This Venn diagram illustrates the left join of two result sets:

To get the rows that available only in the left table but not in the right table, you add a clause to the above query:

And the this Venn diagram illustrates the left join that selects rows available only in the left table:

Проще простого – Inner

Первый вариант – это использование простого объединения. Встречается на практике чаще всего. Возвращает пересечение нескольких (обычно двух) множеств. В табличной терминологии — происходит возврат записи, которая имеется в обоих таблицах. Оная должна соответствовать выставленному заранее критерию.

Для реализации поставленной задачи применяется INNER JOIN. Чтобы лучше понимать данный процесс, стоит составить диаграмму Венна:

• нарисовать круг – пусть это будет «таблица 1»;
• рядом, задевая область первой фигуры, изобразить второй круг – это «таблица 2»;
• закрасить область пересечения – это и есть результат операции inner join.

Рисунок выше – наглядный пример диаграммы Венна. С его помощью удастся разобраться в принципах «простого» объединения нескольких табличек.

Запись

Для того, чтобы выполнять те или иные операции в запросовом языке, стоит уточнить синтаксис желаемой команды. В случае с обычным (простым) объединением необходимо использовать следующий шаблон:

Здесь никаких проблем возникнуть не должно. Код легко читается:

• выбрать колонки;
• from table1 – из первой таблицы;
• объединить с таблицей 2.

Для наглядного примера стоит рассмотреть несколько таблиц с информацией, а также принцип joins типа inner.

Наглядный пример

Пусть будет дана таблица под названием customer. В ней такая информация:

Далее – создается таблица orders:

Теперь нужно joining поля в этих хранилищах информации простым объединением. Для реализации поставленной задачи составляется команда типа:

SELECT customers.customer_id,orders.order_id,orders.order_dateFROM customersINNER JOIN ordersON customers.customer_id = orders.customer_idORDER BY customers.customer_id;

В конечном итоге при обработке запроса на экран будет выведен следующий результат:

Здесь выбираются поля в таблице, которые имеют одинаковые значения customer_id в обоих хранилищах. Поэтому другие поля будут пропущены. А строчка, где order_id = 5 в orders  опускается, так как customer_id идут со значениями null. То есть, их нет в customers.

Саязанные таблицы в стиле MS

Объединение по стандарту SQL, который мы рассматривали в главе 2.7, описывает условие связи в секции WHERE. В MS зачем-то связи перенесли в секцию FROM. На мой взгляд, это как минимум не удобно для создания и для чтения связей. Стандартный вариант намного проще и удобнее. И все же, метод MS мы рассмотрим, ведь только с его помощью в MS Access можно создать левое или правое объединение, и этот же метод поддерживается в MS SQL Server.

Ортогональное объединение по методу MS, т.е. без указания связи:

SELECT * 
FROM tbPeoples CROSS JOIN tbPosition

Внутреннее объединение (эквивалентно знаку равенства) по методу MS описывается следующим образом:

SELECT * 
FROM tbPeoples pl INNER JOIN tbPosition ps 
     ON pl.idPosition=ps.idPosition

Как видите, для этого метода не нужна секция WHERE, но зато намного больше всего нужно писать. Вначале мы описываем, что нам нужно внутреннее объединение (INNER JOIN). Слева и справа от этого оператора указываются таблицы, которые нужно связать. После этого ставиться ключевое слово ON, и только теперь наводим связь между полями связанных таблиц. Таким образом, этот запрос эквивалентен следующему:

SELECT * 
FROM tbPeoples pl, tbPosition ps 
WHERE pl.idPosition=ps.idPosition

Самая большая путаница начинается, когда нужно объединить три таблицы в одно целое. Посмотрите на следующий запрос:

SELECT * 
FROM tbPeoples pl LEFT OUTER JOIN tbPhoneNumbers pn
     ON pl.idPeoples=pn.idPeoples
     INNER JOIN tbPosition ps 
     ON pl.idPosition=ps.idPosition

Сначала объединяются таблицы tbPeoples и tbPhoneNumbers через внешнее левое объединение (LEFT OUTER JOIN). Затем указывается связь между этими таблицами. А вот теперь результат объединение, связываем внутренним объединением (INNER JOIN) с таблицей tbPosition. Внимательно осмотрите запрос, чтобы понять его формат, и что в нем происходит.

Чтобы получить правое объединение, необходимо просто поменять перечисление таблиц местами:

SELECT * 
FROM tbPhoneNumbers pn RIGHT OUTER JOIN tbPeoples pl 
     ON pl.idPeoples=pn.idPeoples
     INNER JOIN tbPosition ps 
     ON pl.idPosition=ps.idPosition

Меняется местами перечисление таблиц, а вот порядок указания связанных полей не имеет особого значения и здесь ничего не меняется.

Если честно, то мне не очень нравиться объединение по методу Microsoft. Какое-то оно неудобное и громоздкое. Даже не знаю, зачем его придумали, когда в стандарте есть все то же самое, только намного проще и нагляднее.

SQL Challenge: Complex Query with Joins and Subqueries

Let’s take everything we’ve learned before and use it to write a more complex query. It’s not uncommon to find that ‘thinking in SQL’ takes a bit of getting used to, so don’t be discouraged if this query seems difficult to understand at first. It will get easier with practice!

When you’re writing complex queries with joins and subqueries, it helps to follow this process:

• Think about what data you need in your final output
• Work out which tables you’ll need to join, and whether you will need to join to a subquery.
• Then start writing your SELECT clause, followed by the join and any other clauses you will need.
• Don’t be afraid to write your query in steps, running it as you go— for instance you can run your subquery as a ‘stand alone’ query first to make sure it looks like you want before writing the outer query.

We will be writing a query to find the countries where the urban center (city) population is more than half of the country’s total population. There are multiple ways to write this query but we’ll step through one approach.

We can start by writing a query to sum all the urban populations for cities in each country. We can do this without a join by grouping on the (we’ll use a limit in our example below to make the output managable):

facts_id	urban_pop
1	3097000
10	172000
100	1127000
101	5000
102	546000

Next, we’ll join the table to that subquery, selecting the country name, urban population and total population (again, we’ve used a limit to keep things tidy):

country	urban_pop	total_pop
Afghanistan	3097000	32564342
Austria	172000	8665550
Libya	1127000	6411776
Liechtenstein	5000	37624
Lithuania	546000	2884433

Lastly, we’ll create a new column that divides the urban population by the total population, and use a and to filter/rank the results:

country	urban_pop	total_pop	urban_pct
Uruguay	1672000	3341893	0.500315
Congo, Republic of the	2445000	4755097	0.514185
Brunei	241000	429646	0.560927
New Caledonia	157000	271615	0.578024
Virgin Islands	60000	103574	0.579296
Falkland Islands (Islas Malvinas)	2000	3361	0.595061
Djibouti	496000	828324	0.598800
Australia	13789000	22751014	0.606083
Iceland	206000	331918	0.620635
Israel	5226000	8049314	0.649248
United Arab Emirates	3903000	5779760	0.675288
Puerto Rico	2475000	3598357	0.687814
Bahamas, The	254000	324597	0.782509
Kuwait	2406000	2788534	0.862819
Saint Pierre and Miquelon	5000	5657	0.883861
Guam	169000	161785	1.044596
Northern Mariana Islands	56000	52344	1.069846
American Samoa	64000	54343	1.177705

You can see that while our final query is complex, it’s much easier to understand if you build it step-by-step.