SQL left join vs несколько таблиц в строке FROM?

Ответ 1

Старый синтаксис с просто перечислением таблиц и использованием предложения WHERE для указания критериев соединения устарел в большинстве современных баз данных.

Это не просто для показа, старый синтаксис имеет возможность быть неоднозначным, если вы используете как INNER, так и OUTER в одном запросе.

Позвольте мне привести пример.

Предположим, что у вас есть 3 таблицы в вашей системе:

Company
Department
Employee

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

Итак, теперь вы хотите сделать следующее:

Перечислите все компании и включите все их отделы и всех своих сотрудников. Обратите внимание, что в некоторых компаниях еще нет отделов, но обязательно включите их. Убедитесь, что вы только извлекаете отделы, в которых есть сотрудники, но всегда указывайте все компании.

Итак, вы делаете это:

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

Обратите внимание, что в последнем есть внутреннее соединение, чтобы выполнить критерии, по которым вам нужны только отделы с людьми.

Хорошо, так что происходит сейчас. Ну, проблема в том, что это зависит от механизма базы данных, оптимизатора запросов, индексов и статистики таблиц. Позвольте мне объяснить.

Если оптимизатор запросов определяет, что способ сделать это - сначала взять компанию, затем найти отделы, а затем сделать внутреннее соединение с сотрудниками, вы не будете получать какие-либо компании, у которых нет отделов.

Причиной этого является то, что предложение WHERE определяет, какие строки заканчиваются в конечном результате, а не отдельные части строк.

И в этом случае из-за левого соединения столбец Department.ID будет NULL, и, таким образом, когда дело доходит до INNER JOIN Employee, нет способа выполнить это ограничение для строки Employee, и поэтому не появится.

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

Таким образом, старый синтаксис неоднозначен. Нет способа указать, что вы хотите, не имея дело с подсказками запросов, а некоторые базы данных вообще не имеют никакого отношения.

Введите новый синтаксис, с помощью которого вы можете выбрать.

Например, если вы хотите, чтобы все компании, как указано в описании проблемы, вот что вы могли бы написать:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

Здесь вы указываете, что хотите, чтобы сотрудник отдела-сотрудника присоединился к одному соединению, а затем присоединился к результатам этого с компаниями.

Кроме того, скажем, вам нужны только отделы, содержащие букву X от их имени. Опять же, если присоединиться к старому стилю, вы рискуете потерять и компанию, если в ней нет отделов с именем X, но с новым синтаксисом вы можете сделать это:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

Это дополнительное предложение используется для соединения, но не является фильтром для всей строки. Таким образом, строка может отображаться с информацией о компании, но может иметь NULL во всех столбцах отдела и сотрудника для этой строки, потому что нет отдела с X в его имени для этой компании. Это сложно со старым синтаксисом.

Вот почему среди других поставщиков Microsoft устарела устаревшим синтаксисом внешнего соединения, но не старым синтаксисом внутреннего соединения, так как SQL Server 2005 и выше. Единственный способ поговорить с базой данных, работающей на Microsoft SQL Server 2005 или 2008, с использованием синтаксиса внешнего соединения старого стиля - установить эту базу данных в режиме совместимости с 8.0 (также как SQL Server 2000).

Кроме того, старый способ, бросив кучу таблиц в оптимизаторе запросов, с кучей предложений WHERE, был сродни словам "вот вы, делайте все возможное". С новым синтаксисом оптимизатор запросов имеет меньше работы, чтобы выяснить, какие части идут вместе.

Итак, у вас есть это.

LEFT и INNER JOIN - это волна будущего.

Ответ 2

Синтаксис JOIN сохраняет условия рядом с таблицей, к которой они применяются. Это особенно полезно, если вы присоединяетесь к большому количеству таблиц.

Кстати, вы также можете сделать внешнее соединение с первым синтаксисом:

WHERE a.x = b.x(+)

или

WHERE a.x *= b.x

или

WHERE a.x = b.x or a.x not in (select x from b)

Ответ 3

Первый способ - это более старый стандарт. Второй метод был введен в SQL-92, http://en.wikipedia.org/wiki/SQL. Полный стандарт можно посмотреть на http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt.

Прошло много лет, прежде чем компании баз данных приняли стандарт SQL-92.

Таким образом, причина, почему второй метод является предпочтительным, является стандартом SQL в соответствии с комитетом стандартов ANSI и ISO.

Ответ 4

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

Кроме того, когда вы позже должны присоединиться к левому соединению, для обслуживания полезно, чтобы все они были в одной структуре. И старый синтаксис устарел с 1992 года, хорошо прошло время, чтобы прекратить его использовать.

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

Ответ 5

В принципе, когда предложение FROM отображает такие таблицы:

SELECT * FROM
  tableA, tableB, tableC

результат является перекрестным произведением всех строк в таблицах A, B, C. Затем вы применяете ограничение WHERE tableA.id = tableB.a_id, которое выкинет огромное количество строк, затем далее... AND tableB.id = tableC.b_id, и вы должны затем получите только те строки, которые вам действительно интересны.

СУБД знают, как оптимизировать этот SQL, чтобы разница в производительности при написании этого с использованием JOINs была незначительной (если таковая имеется). Использование нотации JOIN делает инструкцию SQL более читаемой (IMHO, не используя объединения, превращает оператор в беспорядок). Используя кросс-продукт, вам необходимо указать критерии присоединения в предложении WHERE и проблему с обозначением. Вы переполняете предложение WHERE такими вещами, как

    tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id 

который используется только для ограничения перекрестного произведения. Предложение WHERE должно содержать только ОГРАНИЧЕНИЯ к набору результатов. Если вы смешаете критерии соединения с ограничениями на основе результатов, вы (и другие) найдете ваш запрос более трудным для чтения. Вы должны обязательно использовать JOINs и оставить предложение FROM предложением FROM, а предложение WHERE - предложением WHERE.

Ответ 6

Я думаю, что на этой странице есть несколько веских причин принять второй метод - использование явных JOIN. Еще одним признаком является то, что когда критерии JOIN удаляются из предложения WHERE, становится намного легче увидеть оставшиеся критерии выбора в предложении WHERE.

В действительно сложных операторах SELECT читателю становится намного легче понять, что происходит.

Ответ 7

Синтаксис SELECT * FROM table1, table2, ... подходит для нескольких таблиц, но он становится экспоненциально (не обязательно математически точным), который сложнее и труднее читать по мере увеличения количества таблиц.

Синтаксис JOIN сложнее записать (в начале), но он явно указывает, какие критерии влияют на таблицы. Это затрудняет ошибку.

Кроме того, если все соединения INNER, то обе версии эквивалентны. Однако, как только вы присоедините OUTER в любом месте инструкции, все становится намного сложнее и фактически гарантирует, что то, что вы пишете, не будет запрашивать то, что, по вашему мнению, вы написали.

Ответ 8

Когда вам нужно внешнее соединение, требуется второй синтаксис не:

Oracle:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x = b.x(+)

MSSQLServer (хотя он был устарел в версии 2000)/Sybase:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x *= b.x

Но вернемся к вашему вопросу. Я не знаю ответа, но, вероятно, это связано с тем, что join является более естественным (по крайней мере, синтаксически), чем добавление выражения в предложение where когда вы делаете именно это: соединение.

Ответ 9

Я слышал, как многие люди жалуются, что первый из них слишком сложно понять и что это неясно. Я не вижу в этом проблемы, но после этого обсуждения я использую второй вариант даже для INNER JOINS для ясности.

Ответ 10

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

Ответ 11

Ну, первый и второй запросы могут давать разные результаты, потому что LEFT JOIN включает все записи из первой таблицы, даже если в правой таблице нет соответствующих записей.

Ответ 12

    <ul  id="da-thumbs" class="da-thumbs">
                    <?php $query = mysql_query("select * from teacher_class
                    LEFT JOIN class ON class.class_id = teacher_class.class_id
                    LEFT JOIN subject ON subject.subject_id = teacher_class.subject_id
                    where teacher_id = '$session_id' and school_year = '$school_year' ")or die(mysql_error());
                    $count = mysql_num_rows($query);

                    if ($count > 0){
                    while($row = mysql_fetch_array($query)){
                    $id = $row['teacher_class_id'];

                    ?>
                        <li id="del<?php echo $id; ?>">
                            <a href="my_students.php<?php echo '?id='.$id; ?>">
                                    <img src ="../<?php echo $row['thumbnails'] ?>" width="124" height="140" class="img-polaroid" alt="">
                                <div>
                                <span><p><?php echo $row['class_name']; ?></p></span>
                                </div>
                            </a>
                            <p class="class"><?php echo $row['class_name']; ?></p>
                            <p class="subject"><?php echo $row['subject_code']; ?></p>
                            <a href="#<?php echo $id; ?>" data-toggle="modal"><i class="icon-trash"></i> Hapus</a>  

                        </li>
                    <?php include("modal_hapus_kelas.php"); ?>
                <?php } }else{ ?>
                <div class="alert alert-info"><i class="icon-info-sign"></i> Tidak ada kelas</div>
                <?php  } ?>
                </ul>