sql-запрос возвращает данные из нескольких таблиц

<p>я хотел бы знать следующее:</p><ul> <li>как получить данные из нескольких таблиц в моей базе данных?</li> <li>какие методы существуют для этого?</li> <li>что такое объединения и союзы и чем они отличаются друг от друга?</li> <li>когда я должен использовать каждый из них по сравнению с другими?</li> </ul><p>я планирую использовать это в моем (например, php) приложении, но не хочу выполнять несколько запросов к базе данных, какие варианты у меня есть, чтобы получить данные из нескольких таблиц в одном запросе?</p><p>примечание: я пишу это так, как мне хотелось бы, чтобы я мог дать ссылку на хорошо написанное руководство по многочисленным вопросам, с которыми я постоянно сталкиваюсь в очереди php, поэтому я могу сослаться на это для более подробной информации, когда я отправляю ответ.</p><p>ответы покрывают следующее:</p><ol> <li> <a href="https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12475851#12475851">часть 1 - объединения и союзы</a> </li> <li> <a href="https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12492267#12492267">часть 2 - подзапросы</a> </li> <li> <a href="https://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables/12508381#12508381">часть 3 - уловки и эффективный код</a> </li> <li> <a href="https://stackoverflow.com/a/12542115/1450077">часть 4 - подзапросы в предложении from</a> </li> <li> <a href="https://stackoverflow.com/a/12586263/1450077">часть 5 - смешанная сумка с хитростями джона</a> </li> </ol>

<h1>часть 1 - объединения и союзы</h1><p>этот ответ охватывает:</p><ol> <li>часть 1 <ul> <li>соединение двух или более таблиц с использованием внутреннего соединения (дополнительную информацию см. в <a href="http://en.wikipedia.org/wiki/join\_%28sql%29">запись в википедии</a> )</li> <li>как использовать объединенный запрос</li> <li>внешнее соединение слева и справа (этот <a href="https://stackoverflow.com/a/4715847/1450077">ответ stackoverflow</a> отлично подходит для описания типов соединений)</li> <li>пересечение запросов (и как воспроизвести их, если ваша база данных не поддерживает их) - это функция sql-сервера ( <a href="https://stackoverflow.com/questions/148795/what-is-the-standard-sql-query-to-retrieve-the-intersection-of-tables">см. информацию</a> ) и часть <a href="https://stackoverflow.com/questions/12443904/creating-query-that-returns-id-if-condition-is-matched-in-rows-from-two-tables/12455505#12455505">причина, по которой я написал это полностью вещь</a> в первую очередь.</li> </ul> </li> <li>часть 2 <ul> <li>подзапросы - что это такое, где их можно использовать и на что обратить внимание</li> <li>декартов присоединяется к ака - о, несчастье!</li> </ul> </li> </ol><p>существует несколько способов получения данных из нескольких таблиц в базе данных. в этом ответе я буду использовать синтаксис соединения ansi-92. это может отличаться от ряда других учебных пособий, в которых используется более старый синтаксис ansi-89 (и если вы привыкли к 89, это может показаться гораздо менее интуитивным - но все, что я могу сказать, это попробовать) как есть <em> намного </em> легче понять, когда запросы становятся более сложными. зачем использовать это? есть ли прирост производительности? <a href="https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89">краткий ответ</a> нет, но его <em> </em> легче читать, когда вы к нему привыкнете. с этим синтаксисом легче читать запросы, написанные другими людьми.</p><p>я также собираюсь использовать концепцию маленького каряда, у которого есть база данных, чтобы отслеживать, какие автомобили у него есть. владелец нанял вас в качестве своего специалиста по ит-компьютерам и ожидает, что вы сможете сбросить ему данные, которые он запрашивает, без промедления.</p><p>я сделал несколько таблиц поиска, которые будут использоваться в финальной таблице. это даст нам разумную модель для работы. для начала я буду выполнять запросы к образцу базы данных, которая имеет следующую структуру. я постараюсь подумать об общих ошибках, которые допускаются при запуске, и объясню, что с ними не так, а также, конечно, покажу, как их исправить.</p><p>первая таблица - это просто список цветов, чтобы мы знали, какие цвета у нас на автомобильном дворе.</p><pre><code>mysql> create table colors(id int(3) not null auto\_increment primary key, -> color varchar(15), paint varchar(10)); query ok, 0 rows affected (0.01 sec) mysql> show columns from colors; +-------+-------------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +-------+-------------+------+-----+---------+----------------+ | id | int(3) | no | pri | null | auto\_increment | | color | varchar(15) | yes | | null | | | paint | varchar(10) | yes | | null | | +-------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> insert into colors (color, paint) values ('red', 'metallic'), -> ('green', 'gloss'), ('blue', 'metallic'), -> ('white' 'gloss'), ('black' 'gloss'); query ok, 5 rows affected (0.00 sec) records: 5 duplicates: 0 warnings: 0 mysql> select * from colors; +----+-------+----------+ | id | color | paint | +----+-------+----------+ | 1 | red | metallic | | 2 | green | gloss | | 3 | blue | metallic | | 4 | white | gloss | | 5 | black | gloss | +----+-------+----------+ 5 rows in set (0.00 sec) </code></pre><p>таблица брендов определяет различные марки автомобилей, которые может продать caryard.</p><pre><code>mysql> create table brands (id int(3) not null auto\_increment primary key, -> brand varchar(15)); query ok, 0 rows affected (0.01 sec) mysql> show columns from brands; +-------+-------------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +-------+-------------+------+-----+---------+----------------+ | id | int(3) | no | pri | null | auto\_increment | | brand | varchar(15) | yes | | null | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> insert into brands (brand) values ('ford'), ('toyota'), -> ('nissan'), ('smart'), ('bmw'); query ok, 5 rows affected (0.00 sec) records: 5 duplicates: 0 warnings: 0 mysql> select * from brands; +----+--------+ | id | brand | +----+--------+ | 1 | ford | | 2 | toyota | | 3 | nissan | | 4 | smart | | 5 | bmw | +----+--------+ 5 rows in set (0.00 sec) </code></pre><p>таблица моделей будет охватывать различные типы автомобилей, для этого будет проще использовать различные типы автомобилей, а не фактические модели автомобилей.</p><pre><code>mysql> create table models (id int(3) not null auto\_increment primary key, -> model varchar(15)); query ok, 0 rows affected (0.01 sec) mysql> show columns from models; +-------+-------------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +-------+-------------+------+-----+---------+----------------+ | id | int(3) | no | pri | null | auto\_increment | | model | varchar(15) | yes | | null | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> insert into models (model) values ('sports'), ('sedan'), ('4wd'), ('luxury'); query ok, 4 rows affected (0.00 sec) records: 4 duplicates: 0 warnings: 0 mysql> select * from models; +----+--------+ | id | model | +----+--------+ | 1 | sports | | 2 | sedan | | 3 | 4wd | | 4 | luxury | +----+--------+ 4 rows in set (0.00 sec) </code></pre><p>и, наконец, связать все эти другие столы, стол, который связывает все воедино. поле идентификатора фактически является уникальным номером лота, используемым для идентификации автомобилей.</p><pre><code>mysql> create table cars (id int(3) not null auto\_increment primary key, -> color int(3), brand int(3), model int(3)); query ok, 0 rows affected (0.01 sec) mysql> show columns from cars; +-------+--------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +-------+--------+------+-----+---------+----------------+ | id | int(3) | no | pri | null | auto\_increment | | color | int(3) | yes | | null | | | brand | int(3) | yes | | null | | | model | int(3) | yes | | null | | +-------+--------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1), -> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1); query ok, 10 rows affected (0.00 sec) records: 10 duplicates: 0 warnings: 0 mysql> select * from cars; +----+-------+-------+-------+ | id | color | brand | model | +----+-------+-------+-------+ | 1 | 1 | 2 | 1 | | 2 | 3 | 1 | 2 | | 3 | 5 | 3 | 1 | | 4 | 4 | 4 | 2 | | 5 | 2 | 2 | 3 | | 6 | 3 | 5 | 4 | | 7 | 4 | 1 | 3 | | 8 | 2 | 2 | 1 | | 9 | 5 | 2 | 3 | | 10 | 4 | 5 | 1 | +----+-------+-------+-------+ 10 rows in set (0.00 sec) </code></pre><p>это даст нам достаточно данных (я надеюсь), чтобы охватить приведенные ниже примеры различных типов объединений, а также даст достаточно данных, чтобы сделать их полезными.</p><p>таким образом, чтобы разобраться в этом, босс хочет знать <em> идентификаторы всех спортивных автомобилей, которые у него есть </em>.</p><p>это простое соединение за двумя столами. у нас есть таблица, которая идентифицирует модель и таблицу с имеющимся запасом в ней. как видите, данные в столбце <code>model</code> таблицы <code>cars</code> относятся к столбцу <code>models</code> таблицы <code>cars</code>, которая у нас есть. теперь мы знаем, что таблица моделей имеет идентификатор <code>1</code> для <code>sports</code>, поэтому давайте напишем объединение.</p><pre><code>select id, model from cars join models on model=id </code></pre><p>так что этот запрос выглядит хорошо, верно? мы определили две таблицы и содержат необходимую нам информацию, а также используем соединение, которое правильно определяет столбцы для объединения.</p><pre><code>error 1052 (23000): column 'id' in field list is ambiguous </code></pre><p>о нет! ошибка в нашем первом запросе! да и это слива. видите ли, у запроса действительно есть правильные столбцы, но некоторые из них существуют в обеих таблицах, поэтому база данных запутывается в том, какой фактический столбец мы имеем в виду и где. есть два решения, чтобы решить эту проблему. первый - "хороший и простой", мы можем использовать <code>tablename.columnname</code>, чтобы сообщить базе данных, что именно мы имеем в виду, например:</p><pre><code>select cars.id, models.model from cars join models on cars.model=models.id +----+--------+ | id | model | +----+--------+ | 1 | sports | | 3 | sports | | 8 | sports | | 10 | sports | | 2 | sedan | | 4 | sedan | | 5 | 4wd | | 7 | 4wd | | 9 | 4wd | | 6 | luxury | +----+--------+ 10 rows in set (0.00 sec) </code></pre><p>другой, вероятно, чаще используется и называется псевдонимами таблиц. таблицы в этом примере имеют хорошие и короткие простые имена, но ввод чего-то наподобие <code>kpi\_daily\_sales\_by\_department</code>, вероятно, быстро устареет, поэтому простым способом является присвоение псевдониму таблицы следующим образом:</p><pre><code>select a.id, b.model from cars a join models b on a.model=b.id </code></pre><p>теперь вернемся к запросу. как вы видите, у нас есть информация, которая нам нужна, но у нас также есть информация, которая не запрашивалась, поэтому нам нужно включить в заявление пункт where, чтобы получить спортивные автомобили только в соответствии с запросом. поскольку я предпочитаю метод псевдонимов таблиц, а не использовать имена таблиц снова и снова, я буду придерживаться его с этого момента.</p><p>понятно, что нам нужно добавить предложение where в наш запрос. мы можем идентифицировать спортивные автомобили либо по <code>id=1</code>, либо <code>model='sports'</code>. так как идентификатор индексируется и является первичным ключом (и, как оказалось, он меньше набирает), давайте использовать это в нашем запросе.</p><pre><code>select a.id, b.model from cars a join models b on a.model=b.id where b.id=1 +----+--------+ | id | model | +----+--------+ | 1 | sports | | 3 | sports | | 8 | sports | | 10 | sports | +----+--------+ 4 rows in set (0.00 sec) </code></pre><p>бинго! босс счастлив. конечно, будучи начальником и никогда не радуясь тому, о чем он просил, он смотрит на информацию, затем говорит <em> я тоже хочу цвета </em>.</p><p>итак, у нас уже есть хорошая часть нашего запроса, но нам нужно использовать третью таблицу, которая является цветами. теперь в нашей главной информационной таблице <code>cars</code> хранится идентификатор цвета автомобиля, и он ссылается на столбец идентификатора цвета. итак, аналогично оригиналу, мы можем присоединиться к третьей таблице:</p><pre><code>select a.id, b.model from cars a join models b on a.model=b.id join colors c on a.color=c.id where b.id=1 +----+--------+ | id | model | +----+--------+ | 1 | sports | | 3 | sports | | 8 | sports | | 10 | sports | +----+--------+ 4 rows in set (0.00 sec) </code></pre><p>черт, хотя таблица была правильно соединена и связанные столбцы были связаны, мы забыли извлечь фактическую <em> информацию </em> из новой таблицы, которую мы только что связали.</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id where b.id=1 +----+--------+-------+ | id | model | color | +----+--------+-------+ | 1 | sports | red | | 8 | sports | green | | 10 | sports | white | | 3 | sports | black | +----+--------+-------+ 4 rows in set (0.00 sec) </code></pre><p>да, это босс с нашей спины на мгновение. теперь, чтобы объяснить это немного подробнее. как вы можете видеть, предложение <code>from</code> в нашем выражении связывает нашу основную таблицу (я часто использую таблицу, которая содержит информацию, а не таблицу поиска или измерения. запрос будет работать так же хорошо, как и все таблицы, но не имеет смысла когда мы возвращаемся к этому запросу, чтобы прочитать его через несколько месяцев, поэтому часто лучше попытаться написать запрос, который будет хорошим и простым для понимания - выложите его интуитивно, используйте хороший отступ, чтобы все было так же ясно как это может быть. если вы продолжаете учить других, попробуйте привить эти характеристики в их запросах - особенно, если вы будете их устранять.</p><p>таким образом, можно продолжать связывать все новые и новые таблицы.</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id where b.id=1 </code></pre><p>хотя я забыл включить таблицу, в которую мы можем захотеть объединить более одного столбца, в оператор <code>join</code>, вот пример. если таблица <code>models</code> имеет модели, специфичные для бренда, и, следовательно, также имеет столбец с именем <code>brand</code>, который связан с таблицей <code>brands</code> в поле <code>id</code>, это можно сделать следующим образом:</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id and b.brand=d.id where b.id=1 </code></pre><p>как видите, приведенный выше запрос не только связывает объединенные таблицы с основной таблицей <code>cars</code>, но также определяет соединения между уже объединенными таблицами. если это не было сделано, результат называется декартовым соединением - что означает dba плохо. декартово соединение - это то, где строки возвращаются, потому что информация не говорит базе данных, как ограничить результаты, поэтому запрос возвращает <em> все </em> строки, которые соответствуют критериям.</p><p>итак, чтобы привести пример декартового объединения, давайте запустим следующий запрос:</p><pre><code>select a.id, b.model from cars a join models b +----+--------+ | id | model | +----+--------+ | 1 | sports | | 1 | sedan | | 1 | 4wd | | 1 | luxury | | 2 | sports | | 2 | sedan | | 2 | 4wd | | 2 | luxury | | 3 | sports | | 3 | sedan | | 3 | 4wd | | 3 | luxury | | 4 | sports | | 4 | sedan | | 4 | 4wd | | 4 | luxury | | 5 | sports | | 5 | sedan | | 5 | 4wd | | 5 | luxury | | 6 | sports | | 6 | sedan | | 6 | 4wd | | 6 | luxury | | 7 | sports | | 7 | sedan | | 7 | 4wd | | 7 | luxury | | 8 | sports | | 8 | sedan | | 8 | 4wd | | 8 | luxury | | 9 | sports | | 9 | sedan | | 9 | 4wd | | 9 | luxury | | 10 | sports | | 10 | sedan | | 10 | 4wd | | 10 | luxury | +----+--------+ 40 rows in set (0.00 sec) </code></pre><p>боже мой, это безобразно. тем не менее, что касается базы данных, это <em> именно то </em>, что было запрошено. в запросе мы запросили <code>id</code> от <code>cars</code> и <code>model</code> от <code>models</code>. однако, поскольку мы не указали <em>, как </em> объединять таблицы, база данных соответствовала <em> каждому </em> строка из первой таблицы с <em> каждой </em> строкой из второй таблицы.</p><p>итак, босс вернулся, и ему снова нужна дополнительная информация. <em> я хочу тот же список, но также включить в него 4wd </em>.</p><p>это, однако, дает нам отличный повод взглянуть на два разных способа сделать это. мы могли бы добавить еще одно условие к предложению where, например:</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id where b.id=1 or b.id=3 </code></pre><p>хотя вышеперечисленное будет работать отлично, давайте рассмотрим его по-другому, но это отличный повод показать, как будет работать запрос <code>union</code>.</p><p>мы знаем, что следующее вернет все спортивные автомобили:</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id where b.id=1 </code></pre><p>и следующее вернет все 4wd:</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id where b.id=3 </code></pre><p>таким образом, добавив между ними предложение <code>union all</code>, результаты второго запроса будут добавлены к результатам первого запроса.</p><pre><code>select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id where b.id=1 union all select a.id, b.model, c.color from cars a join models b on a.model=b.id join colors c on a.color=c.id join brands d on a.brand=d.id where b.id=3 +----+--------+-------+ | id | model | color | +----+--------+-------+ | 1 | sports | red | | 8 | sports | green | | 10 | sports | white | | 3 | sports | black | | 5 | 4wd | green | | 7 | 4wd | white | | 9 | 4wd | black | +----+--------+-------+ 7 rows in set (0.00 sec) </code></pre><p>как видите, результаты первого запроса возвращаются первыми, а затем результаты второго запроса.</p><p>в этом примере, конечно, было бы намного проще просто использовать первый запрос, но запросы <code>union</code> могут быть полезны для конкретных случаев. они являются отличным способом вернуть конкретные результаты из таблиц из таблиц, которые нелегко объединить - или в этом отношении <em> полностью </em> несвязанных таблиц. однако есть несколько правил, которым нужно следовать.</p><ul> <li>типы столбцов из первого запроса должны совпадать с типами столбцов из любого другого запроса ниже.</li> <li>имена столбцов из первого запроса будут использоваться для идентификации всего набора результатов.</li> <li>количество столбцов в каждом запросе должно быть одинаковым.</li> </ul><p>теперь вы можете <a href="https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all">интересно, в чем разница</a> между использованием <code>union</code> и <code>union all</code>. запрос <code>union</code> удалит дубликаты, а <code>union all</code> - нет. это означает, что при использовании <code>union</code> вместо <code>union all</code> наблюдается небольшое снижение производительности, но результаты могут стоить того, хотя я не буду спекулировать на подобных вещах.</p><p>на этой заметке, возможно, стоит отметить некоторые дополнительные заметки здесь.</p><ul> <li>если мы хотим упорядочить результаты, мы можем использовать <code>order by</code>, но вы больше не можете использовать псевдоним. в приведенном выше запросе добавление <code>order by a.id</code> может привести к ошибке - что касается результатов, столбец называется <code>id</code>, а не <code>a.id</code> - даже если в обоих запросах использовался один и тот же псевдоним.</li> <li>у нас может быть только один оператор <code>order by</code>, и он должен быть последним.</li> </ul><p>в следующих примерах я добавляю несколько дополнительных строк в наши таблицы.</p><p>я добавил <code>holden</code> в таблицу брендов. я также добавил строку в <code>cars</code> со значением <code>color</code> <code>12</code>, которое не имеет ссылки в таблице цветов.</p><p>ладно, босс снова вернулся, выкрикивая запросы - * я хочу подсчитать каждую марку, которую мы несем, и количество автомобилей в ней! `- как правило, мы просто попадаем в интересный раздел нашего обсуждения, и босс хочет больше работы ,.</p><p>rightyo, поэтому первое, что нам нужно сделать, это получить полный список возможных брендов.</p><pre><code>select a.brand from brands a +--------+ | brand | +--------+ | ford | | toyota | | nissan | | smart | | bmw | | holden | +--------+ 6 rows in set (0.00 sec) </code></pre><p>теперь, когда мы присоединяем это к нашей таблице машин, мы получаем следующий результат:</p><pre><code>select a.brand from brands a join cars b on a.id=b.brand group by a.brand +--------+ | brand | +--------+ | bmw | | ford | | nissan | | smart | | toyota | +--------+ 5 rows in set (0.00 sec) </code></pre><p>что, конечно, является проблемой - мы не видим упоминаний о прекрасном бренде <code>holden</code>, который я добавил.</p><p>это связано с тем, что объединение ищет совпадающие строки в <em> обеих </em> таблицах. поскольку в автомобилях нет данных типа <code>holden</code>, они не возвращаются. здесь мы можем использовать соединение <code>outer</code>. это вернет <em> все </em> результаты из одной таблицы, независимо от того, совпадают они с другой таблицей или нет:</p><pre><code>select a.brand from brands a left outer join cars b on a.id=b.brand group by a.brand +--------+ | brand | +--------+ | bmw | | ford | | holden | | nissan | | smart | | toyota | +--------+ 6 rows in set (0.00 sec) </code></pre><p>теперь, когда у нас это есть, мы можем добавить прекрасную функцию агрегирования, чтобы получить счет и на мгновение снять босса с наших плеч.</p><pre><code>select a.brand, count(b.id) as countofbrand from brands a left outer join cars b on a.id=b.brand group by a.brand +--------+--------------+ | brand | countofbrand | +--------+--------------+ | bmw | 2 | | ford | 2 | | holden | 0 | | nissan | 1 | | smart | 1 | | toyota | 5 | +--------+--------------+ 6 rows in set (0.00 sec) </code></pre><p>и с этим, далеко босс скрывается.</p><p>теперь, чтобы объяснить это более подробно, внешние объединения могут быть типа <code>left</code> или <code>right</code>. влево или вправо определяет, какая таблица <em> полностью </em> включена. <code>left outer join</code> будет включать все строки из таблицы слева, а (как вы уже догадались) <code>right outer join</code> объединяет все результаты из таблицы справа в результаты.</p><p>некоторые базы данных допускают <code>full outer join</code>, который будет возвращать результаты (независимо от того, совпадают они или нет) из <em> обеих </em> таблиц, но это поддерживается не во всех базах данных.</p><p>теперь я, вероятно, думаю, что в данный момент вы задаетесь вопросом, можете ли вы объединить типы соединений в запросе - и ответ "да", вы, безусловно, можете.</p><pre><code>select b.brand, c.color, count(a.id) as countofbrand from cars a right outer join brands b on b.id=a.brand join colors c on a.color=c.id group by a.brand, c.color +--------+-------+--------------+ | brand | color | countofbrand | +--------+-------+--------------+ | ford | blue | 1 | | ford | white | 1 | | toyota | black | 1 | | toyota | green | 2 | | toyota | red | 1 | | nissan | black | 1 | | smart | white | 1 | | bmw | blue | 1 | | bmw | white | 1 | +--------+-------+--------------+ 9 rows in set (0.00 sec) </code></pre><p>итак, почему это не те результаты, которые ожидались? это потому, что, хотя мы выбрали внешнее объединение из автомобилей по брендам, оно не было указано в соединении с цветами - так что конкретное объединение будет возвращать только результаты, которые соответствуют в обеих таблицах.</p><p>вот запрос, который будет работать, чтобы получить ожидаемые результаты:</p><pre><code>select a.brand, c.color, count(b.id) as countofbrand from brands a left outer join cars b on a.id=b.brand left outer join colors c on b.color=c.id group by a.brand, c.color +--------+-------+--------------+ | brand | color | countofbrand | +--------+-------+--------------+ | bmw | blue | 1 | | bmw | white | 1 | | ford | blue | 1 | | ford | white | 1 | | holden | null | 0 | | nissan | black | 1 | | smart | white | 1 | | toyota | null | 1 | | toyota | black | 1 | | toyota | green | 2 | | toyota | red | 1 | +--------+-------+--------------+ 11 rows in set (0.00 sec) </code></pre><p>как мы видим, у нас есть два внешних соединения в запросе, и результаты достигаются, как и ожидалось.</p><p>а как насчет других типов соединений, которые вы спрашиваете? как насчет перекрестков?</p><p>ну, не все базы данных поддерживают <code>intersection</code>, но почти все базы данных позволят вам создать пересечение посредством соединения (или, по крайней мере, хорошо структурированного оператора where).</p><p>пересечение - это тип объединения, в некоторой степени похожий на <code>union</code>, как описано выше, но отличие состоит в том, что оно <em> только </em> возвращает строки данных, которые идентичны (и я имею в виду идентичные) между различными отдельными запросами, объединенными объединением. будут возвращены только те строки, которые идентичны во всех отношениях.</p><p>простой пример будет таким:</p><pre><code>select * from colors where id>2 intersect select * from colors where id<4 </code></pre><p>в то время как обычный запрос <code>union</code> будет возвращать все строки таблицы (первый запрос будет возвращать что-либо через <code>id>2</code>, а второй - что-нибудь, имеющее <code>id<4</code>), что приведет к полному набору, запрос на пересечение будет возвращать только строку, соответствующую <code>id=3</code>, поскольку он удовлетворяет обоим критерии.</p><p>теперь, если ваша база данных не поддерживает запрос <code>intersect</code>, вышеизложенное можно легко выполнить с помощью следующего запроса:</p><pre><code>select a.id, a.color, a.paint from colors a join colors b on a.id=b.id where a.id>2 and b.id<4 +----+-------+----------+ | id | color | paint | +----+-------+----------+ | 3 | blue | metallic | +----+-------+----------+ 1 row in set (0.00 sec) </code></pre><p>если вы хотите выполнить пересечение двух разных таблиц, используя базу данных, которая по сути не поддерживает запрос на пересечение, вам нужно будет создать соединение <em> в каждом столбце </em> из таблиц.</p>

<p>хорошо, я нашел этот пост очень интересным, и я хотел бы поделиться некоторыми своими знаниями по созданию запроса. спасибо за это <strong> <a href="https://stackoverflow.com/users/1450077/fluffeh">fluffeh</a> </strong>. другие, кто может прочитать это и могут почувствовать, что я неправ, на 101% свободны редактировать и критиковать мой ответ. (<em> честно говоря, я очень благодарен за исправление моей ошибки. </em>)</p><p>я буду публиковать некоторые часто задаваемые вопросы в теге <code>mysql</code>.</p><hr><h1><strong> трюк № 1 (<em> строк, который соответствует нескольким условиям </em>) </strong></h1><p>учитывая эту схему</p><pre><code>create table movielist ( id int, moviename varchar(25), constraint ml\_pk primary key (id), constraint ml\_uq unique (moviename) ); insert into movielist values (1, 'american pie'); insert into movielist values (2, 'the notebook'); insert into movielist values (3, 'discovery channel: africa'); insert into movielist values (4, 'mr. bean'); insert into movielist values (5, 'expendables 2'); create table categorylist ( movieid int, categoryname varchar(25), constraint cl\_uq unique(movieid, categoryname), constraint cl\_fk foreign key (movieid) references movielist(id) ); insert into categorylist values (1, 'comedy'); insert into categorylist values (1, 'romance'); insert into categorylist values (2, 'romance'); insert into categorylist values (2, 'drama'); insert into categorylist values (3, 'documentary'); insert into categorylist values (4, 'comedy'); insert into categorylist values (5, 'comedy'); insert into categorylist values (5, 'action'); </code></pre><p><strong>вопрос</strong></p><p>найдите <em> все фильмы </em>, которые принадлежат как минимум <em> к обеим категориям </em> <code>comedy</code> и <code>romance</code>.</p><p><strong> решение </strong></p><p>этот вопрос иногда может быть очень сложным. может показаться, что такой запрос будет ответом:</p><pre><code>select distinct a.moviename from movielist a inner join categorylist b on a.id = b.movieid where b.categoryname = 'comedy' and b.categoryname = 'romance' </code></pre><h2> <a href="http://sqlfiddle.com/#!2/b60cb/4">sqlfiddle demo</a> </h2><p>что, безусловно, очень неправильно, потому что это не дает <em> никакого результата </em>. объяснение этому заключается в том, что существует только одно допустимое значение <code>categoryname</code> в <em> каждой строке </em>. например, первое условие возвращает <em> true </em>, второе условие всегда false. таким образом, с помощью оператора <code>and</code> оба условия должны выполняться; в противном случае это будет ложным. другой запрос такой,</p><pre><code>select distinct a.moviename from movielist a inner join categorylist b on a.id = b.movieid where b.categoryname in ('comedy','romance') </code></pre><h2> <a href="http://sqlfiddle.com/#!2/b60cb/2">sqlfiddle demo</a> </h2><p>и результат все еще неверен, потому что он соответствует записи, которая имеет <em> хотя бы одно </em> одно совпадение с <code>categoryname</code>. реальным решением <strong> </strong> <em> было бы подсчет количества экземпляров записи на фильм </em>. номер экземпляра должен соответствовать общему количеству значений, указанных в условии.</p><pre><code>select a.moviename from movielist a inner join categorylist b on a.id = b.movieid where b.categoryname in ('comedy','romance') group by a.moviename having count(*) = 2 </code></pre><h2> <a href="http://sqlfiddle.com/#!2/b60cb/3">sqlfiddle demo (ответ)</a> </h2><ul> <li> <a href="http://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/">sql реляционного деления</a> </li> </ul><hr><h1><strong> трюк № 2 (<em> максимальная запись для каждой записи </em>) </strong></h1><p>данная схема,</p><pre><code>create table software ( id int, softwarename varchar(25), descriptions varchar(150), constraint sw\_pk primary key (id), constraint sw\_uq unique (softwarename) ); insert into software values (1,'paintme','used for photo editing'); insert into software values (2,'world map','contains map of different places of the world'); insert into software values (3,'dictionary','contains description, synonym, antonym of the words'); create table versionlist ( softwareid int, versionno int, datereleased date, constraint sw\_uq unique (softwareid, versionno), constraint sw\_fk foreign key (softwareid) references software(id) ); insert into versionlist values (3, 2, '2009-12-01'); insert into versionlist values (3, 1, '2009-11-01'); insert into versionlist values (3, 3, '2010-01-01'); insert into versionlist values (2, 2, '2010-12-01'); insert into versionlist values (2, 1, '2009-12-01'); insert into versionlist values (1, 3, '2011-12-01'); insert into versionlist values (1, 2, '2010-12-01'); insert into versionlist values (1, 1, '2009-12-01'); insert into versionlist values (1, 4, '2012-12-01'); </code></pre><p><strong>вопрос</strong></p><p>найдите последнюю версию для каждого программного обеспечения. вывести следующие столбцы: <code>softwarename</code>, <code>descriptions</code>, <code>latestversion</code> (<em> из столбца versionno </em>), <code>datereleased</code></p><p><strong> решение </strong></p><p>некоторые разработчики sql по ошибке используют агрегатную функцию <code>max()</code>. они имеют тенденцию создавать, как это,</p><pre><code>select a.softwarename, a.descriptions, max(b.versionno) as latestversion, b.datereleased from software a inner join versionlist b on a.id = b.softwareid group by a.id order by a.id </code></pre><h2> <a href="http://sqlfiddle.com/#!2/89929/2">sqlfiddle demo</a> </h2><p>(<em> большинство субд генерирует синтаксическую ошибку по этому поводу, поскольку не указываются некоторые неагрегированные столбцы в предложении <code>group by</code> </em>), в результате получается корректное <code>latestversion</code> для каждого программного обеспечения, но, очевидно, <code>datereleased</code> неверны. <code>mysql</code> не поддерживает <code>window functions</code> и <code>common table expression</code>, как это уже делают некоторые rdbms. обойти эту проблему можно, создав <code>subquery</code>, которая получает индивидуальный максимум <code>versionno</code> для каждого программного обеспечения, а затем объединяется с другими таблицами.</p><pre><code>select a.softwarename, a.descriptions, b.latestversion, c.datereleased from software a inner join ( select softwareid, max(versionno) latestversion from versionlist group by softwareid ) b on a.id = b.softwareid inner join versionlist c on c.softwareid = b.softwareid and c.versionno = b.latestversion group by a.id order by a.id </code></pre><h2> <a href="http://sqlfiddle.com/#!2/89929/3">sqlfiddle demo (ответ)</a> </h2><hr><p>так вот и все. я опубликую еще один, как только вспомню любой другой <em>faq</em> по тегу <code>mysql</code>. спасибо за чтение этой маленькой статьи. я надеюсь, что вы хотя бы получили хоть немного знаний об этом.</p><p><strong> обновление 1 </strong></p><hr><h1><strong> трюк № 3 (<em> поиск последней записи между двумя идентификаторами </em>) </strong></h1><p>данная схема</p><pre><code>create table userlist ( id int, name varchar(20), constraint us\_pk primary key (id), constraint us\_uq unique (name) ); insert into userlist values (1, 'fluffeh'); insert into userlist values (2, 'john woo'); insert into userlist values (3, 'hims056'); create table conversation ( id int, from\_id int, to\_id int, message varchar(250), deliverydate date ); insert into conversation values (1, 1, 2, 'hi john', '2012-01-01'); insert into conversation values (2, 2, 1, 'hello fluff', '2012-01-02'); insert into conversation values (3, 1, 3, 'hey hims', '2012-01-03'); insert into conversation values (4, 1, 3, 'please reply', '2012-01-04'); insert into conversation values (5, 3, 1, 'how are you?', '2012-01-05'); insert into conversation values (6, 3, 2, 'sample message!', '2012-01-05'); </code></pre><p><strong>вопрос</strong></p><p>найти последний разговор между двумя пользователями.</p><p><strong> решение </strong></p><pre><code>select b.name sendername, c.name recipientname, a.message, a.deliverydate from conversation a inner join userlist b on a.from\_id = b.id inner join userlist c on a.to\_id = c.id where (least(a.from\_id, a.to\_id), greatest(a.from\_id, a.to\_id), deliverydate) in ( select least(from\_id, to\_id) minfrom, greatest(from\_id, to\_id) maxto, max(deliverydate) maxdate from conversation group by minfrom, maxto ) </code></pre><h2> <a href="http://sqlfiddle.com/#!2/b7bd1/1">sqlfiddle demo</a> </h2>

<h1>часть 2 - подзапросы</h1><p>хорошо, теперь босс снова ворвался - <em>я хочу список всех наших автомобилей с маркой и общее количество этой марки у нас!</em></p><p>это прекрасная возможность использовать следующий трюк в нашем наборе sql вкусностей - подзапрос. если вы не знакомы с этим термином, подзапрос - это запрос, который выполняется внутри другого запроса. есть много разных способов их использования.</p><p>для нашего запроса, давайте сначала составим простой запрос, в котором будут перечислены все автомобили и марки:</p><pre><code>select a.id, b.brand from cars a join brands b on a.brand=b.id </code></pre><p>теперь, если мы хотим просто подсчитать количество автомобилей, отсортированных по марке, мы можем, конечно, написать это:</p><pre><code>select b.brand, count(a.id) as countcars from cars a join brands b on a.brand=b.id group by b.brand +--------+-----------+ | brand | countcars | +--------+-----------+ | bmw | 2 | | ford | 2 | | nissan | 1 | | smart | 1 | | toyota | 5 | +--------+-----------+ </code></pre><p>итак, мы должны иметь возможность просто добавить функцию count к нашему исходному запросу, верно?</p><pre><code>select a.id, b.brand, count(a.id) as countcars from cars a join brands b on a.brand=b.id group by a.id, b.brand +----+--------+-----------+ | id | brand | countcars | +----+--------+-----------+ | 1 | toyota | 1 | | 2 | ford | 1 | | 3 | nissan | 1 | | 4 | smart | 1 | | 5 | toyota | 1 | | 6 | bmw | 1 | | 7 | ford | 1 | | 8 | toyota | 1 | | 9 | toyota | 1 | | 10 | bmw | 1 | | 11 | toyota | 1 | +----+--------+-----------+ 11 rows in set (0.00 sec) </code></pre><p>к сожалению, нет, мы не можем этого сделать. причина в том, что когда мы добавляем идентификатор автомобиля (столбец a.id), мы должны добавить его в группу, так что теперь, когда работает функция подсчета, для каждого идентификатора соответствует только один идентификатор.</p><p>однако здесь мы можем использовать подзапрос - фактически мы можем сделать два совершенно разных типа подзапроса, которые будут возвращать те же результаты, которые нам нужны для этого. первый заключается в том, чтобы просто поместить подзапрос в предложение <code>select</code>. это означает, что каждый раз, когда мы получаем строку данных, подзапрос будет выполняться, получать столбец данных и затем вставлять его в наш ряд данных.</p><pre><code>select a.id, b.brand, ( select count(c.id) from cars c where a.brand=c.brand ) as countcars from cars a join brands b on a.brand=b.id +----+--------+-----------+ | id | brand | countcars | +----+--------+-----------+ | 2 | ford | 2 | | 7 | ford | 2 | | 1 | toyota | 5 | | 5 | toyota | 5 | | 8 | toyota | 5 | | 9 | toyota | 5 | | 11 | toyota | 5 | | 3 | nissan | 1 | | 4 | smart | 1 | | 6 | bmw | 2 | | 10 | bmw | 2 | +----+--------+-----------+ 11 rows in set (0.00 sec) </code></pre><p>и бэм !, это сделало бы нас. однако, если вы заметили, этот подзапрос должен будет выполняться для каждой строки данных, которую мы возвращаем. даже в этом небольшом примере у нас всего пять разных марок автомобилей, но подзапрос выполнялся одиннадцать раз, поскольку у нас есть одиннадцать строк данных, которые мы возвращаем. так что в данном случае это не самый эффективный способ написания кода.</p><p>для другого подхода, давайте запустим подзапрос и представим, что это таблица:</p><pre><code>select a.id, b.brand, d.countcars from cars a join brands b on a.brand=b.id join ( select c.brand, count(c.id) as countcars from cars c group by c.brand ) d on a.brand=d.brand +----+--------+-----------+ | id | brand | countcars | +----+--------+-----------+ | 1 | toyota | 5 | | 2 | ford | 2 | | 3 | nissan | 1 | | 4 | smart | 1 | | 5 | toyota | 5 | | 6 | bmw | 2 | | 7 | ford | 2 | | 8 | toyota | 5 | | 9 | toyota | 5 | | 10 | bmw | 2 | | 11 | toyota | 5 | +----+--------+-----------+ 11 rows in set (0.00 sec) </code></pre><p>итак, у нас есть одинаковые результаты (упорядоченные немного по-другому - кажется, что база данных хотела вернуть результаты, упорядоченные по первому столбцу, который мы выбрали на этот раз) - но те же правильные числа.</p><p>итак, в чем разница между ними и когда мы должны использовать каждый тип подзапроса? во-первых, давайте убедимся, что мы понимаем, как работает этот второй запрос. мы выбрали две таблицы в предложении <code>from</code> нашего запроса, а затем написали запрос и сказали базе данных, что на самом деле это была таблица, и база данных полностью ей довольна. <em>может</em> есть некоторые преимущества использования этого метода (а также некоторые ограничения). прежде всего, этот подзапрос выполнялся <em>один раз</em>. если бы наша база данных содержала большой объем данных, вполне может быть значительное улучшение по сравнению с первым методом. однако, поскольку мы используем это как таблицу, мы должны добавить дополнительные строки данных, чтобы они могли быть присоединены к нашим строкам данных. мы также должны быть уверены, что есть <em>достаточно</em> строк данных, если мы собираемся использовать простое соединение, как в запросе выше. если вы помните, объединение будет извлекать только те строки, которые имеют совпадающие данные на <em>обеих</em> сторонах объединения. если мы не будем осторожны, это может привести к тому, что действительные данные не будут возвращены из нашей таблицы автомобилей, если в этом подзапросе не было подходящей строки.</p><p>теперь, оглядываясь на первый подзапрос, также есть некоторые ограничения. поскольку мы собираем данные обратно в одну строку, мы можем <em>только</em> извлечь одну строку данных. подзапросы, используемые в предложении <code>select</code> запроса, очень часто используют только агрегатную функцию, такую как <code>sum</code>, <code>count</code>, <code>max</code> или другая аналогичная агрегатная функция. они не <em>имеют</em> к, но это часто, как они написаны.</p><p>итак, прежде чем мы продолжим, давайте кратко рассмотрим, где еще мы можем использовать подзапрос. мы можем использовать его в предложении <code>where</code> - теперь этот пример немного надуманный, как и в нашей базе данных, есть более эффективные способы получения следующих данных, но если посмотреть только на пример, давайте посмотрим:</p><pre><code>select id, brand from brands where brand like '%o%' +----+--------+ | id | brand | +----+--------+ | 1 | ford | | 2 | toyota | | 6 | holden | +----+--------+ 3 rows in set (0.00 sec) </code></pre><p>это возвращает нам список идентификаторов брендов и названий брендов (второй столбец добавлен только для того, чтобы показать нам бренды), которые содержат в имени букву <code>o</code>.</p><p>теперь мы можем использовать результаты этого запроса в предложении where:</p><pre><code>select a.id, b.brand from cars a join brands b on a.brand=b.id where a.brand in ( select id from brands where brand like '%o%' ) +----+--------+ | id | brand | +----+--------+ | 2 | ford | | 7 | ford | | 1 | toyota | | 5 | toyota | | 8 | toyota | | 9 | toyota | | 11 | toyota | +----+--------+ 7 rows in set (0.00 sec) </code></pre><p>как вы можете видеть, несмотря на то, что подзапрос возвращал три идентификатора бренда, наша таблица автомобилей содержала записи только для двух из них.</p><p>в этом случае, для более подробной информации, подзапрос работает так, как если бы мы написали следующий код:</p><pre><code>select a.id, b.brand from cars a join brands b on a.brand=b.id where a.brand in (1,2,6) +----+--------+ | id | brand | +----+--------+ | 1 | toyota | | 2 | ford | | 5 | toyota | | 7 | ford | | 8 | toyota | | 9 | toyota | | 11 | toyota | +----+--------+ 7 rows in set (0.00 sec) </code></pre><p>опять же, вы можете увидеть, как подзапрос по сравнению с ручным вводом изменил порядок строк при возврате из базы данных.</p><p>пока мы обсуждаем подзапросы, давайте посмотрим, что еще мы можем сделать с подзапросом:</p><ul> <li>вы можете поместить подзапрос в другой подзапрос, и так далее, и так далее. существует предел, который зависит от вашей базы данных, но, за исключением рекурсивных функций некоторых безумных и маниакальных программистов, большинство людей никогда не достигнет этого предела.</li> <li>вы можете поместить несколько подзапросов в один запрос, несколько в предложении <code>select</code>, некоторые в предложении <code>from</code> и еще пару в предложении <code>where</code> - просто помните, что каждый добавленный вами запрос делает ваш запрос более сложным и может занять больше времени, чтобы выполнить.</li> </ul><p>если вам нужно написать какой-то эффективный код, может быть полезно написать запрос несколькими способами и посмотреть (либо по времени, либо с помощью плана объяснения), какой запрос является оптимальным для получения ваших результатов. первый способ, который работает, не всегда может быть лучшим способом сделать это.</p>

<h1>часть 3 - хитрости и эффективный код</h1><h1>mysql в () эффективность</h1><p>я думал, что добавлю несколько дополнительных битов, для подсказок и трюков, которые придумали.</p><p>один вопрос, который я вижу, справедливо задан: <em> как получить несоответствующие строки из двух таблиц </em>, и я вижу ответ, наиболее часто принимаемый как нечто как показано ниже (на основе нашей таблицы автомобилей и марок - в которой <em> холден </em> указан как бренд, но не отображается в таблице автомобилей):</p><pre><code>select a.id, a.brand from brands a where a.id not in(select brand from cars) </code></pre><p>и <em> да </em> это будет работать.</p><pre><code>+----+--------+ | id | brand | +----+--------+ | 6 | holden | +----+--------+ 1 row in set (0.00 sec) </code></pre><p>однако это <em> не </em> эффективно в некоторых базах данных. вот <a href="https://stackoverflow.com/questions/1179231/sql-efficiency-where-in-subquery-vs-join-then-group">ссылка на вопрос переполнения стека</a> об этом, а вот <a href="http://explainextended.com/2009/06/16/in-vs-join-vs-exists/">отличная статья</a> если вы хотите разобраться с мелочами.</p><p>короткий ответ: если оптимизатор не справляется с этим эффективно, может быть гораздо лучше использовать запрос, подобный следующему, чтобы получить несопоставленные строки:</p><pre><code>select a.brand from brands a left join cars b on a.id=b.brand where b.brand is null +--------+ | brand | +--------+ | holden | +--------+ 1 row in set (0.00 sec) </code></pre><h1>обновить таблицу с той же таблицей в подзапросе</h1><p>аааа, еще один старый, но хороший - старый <em> вы не можете указать целевую таблицу 'бренды' для обновления в предложении from </em>.</p><p>mysql не позволит вам выполнить запрос <code>update...</code> с вложенным выбором в той же таблице. теперь вы можете подумать, почему бы просто не вставить это в пункт where, верно? но что, если вы хотите обновить только строку с датой <code>max()</code> среди множества других строк? вы не можете точно сделать это в предложении where.</p><pre><code>update brands set brand='holden' where id= (select id from brands where id=6); error 1093 (hy000): you can't specify target table 'brands' for update in from clause </code></pre><p>итак, мы не можем этого сделать, а? ну, не совсем так. существует хитрый обходной путь, о котором удивительно большое количество пользователей не знает, хотя он включает в себя некоторые хакерские действия, на которые вам нужно будет обратить внимание.</p><p>вы можете вставить подзапрос в другой подзапрос, который создает достаточный промежуток между двумя запросами, чтобы он работал. однако обратите внимание, что может быть безопаснее прикрепить запрос в транзакции - это предотвратит любые другие изменения в таблицах во время выполнения запроса.</p><pre><code>update brands set brand='holden' where id= (select id from (select id from brands where id=6 ) as updatetable); query ok, 0 rows affected (0.02 sec) rows matched: 1 changed: 0 warnings: 0 </code></pre>

<p>вы можете использовать концепцию множественных запросов в ключевом слове from. позвольте мне показать вам один пример:</p><pre><code>select distinct e.id,e.name,d.name,lap.lappy laptop\_make,c\_loc.cnty county from ( select c.id cnty,l.name from county c, location l where c.id=l.county\_id and l.end\_date is not null ) c\_loc, emp e inner join dept d on e.deptno =d.id left join ( select l.id lappy, c.name cmpy from laptop l, company c where l.make = c.name ) lap on e.cmpy\_id=lap.cmpy </code></pre><p>вы можете использовать столько таблиц, сколько захотите. используйте внешние объединения и объединения там, где это необходимо, даже внутри подзапросов таблиц.</p><p>это очень простой способ задействовать столько таблиц и полей.</p>

<p>надеюсь, что это заставит его находить таблицы, когда вы читаете эту вещь:</p><p><strong> <a href="http://jsfiddle.net/1wzr8b05/">jsfiddle</a> </strong></p><pre><code>mysql> show columns from colors; +-------+-------------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +-------+-------------+------+-----+---------+----------------+ | id | int(3) | no | pri | null | auto\_increment | | color | varchar(15) | yes | | null | | | paint | varchar(10) | yes | | null | | +-------+-------------+------+-----+---------+----------------+ </code></pre>