深入解析嵌套子查询、关联子查询与连接操作的差异及应用场景

原文:https://www . geesforgeks . org/嵌套子查询相关子查询和连接操作之间的差异/

Join 操作:
Join 操作是一种二进制操作，用于根据两个或多个表之间的公共字段来组合它们的数据或行。内部连接、左连接、右连接、完全连接是不同类型的连接。

示例–

查找已订购客户的详细信息。

子查询
当一个查询包含在另一个查询中时，外部查询称为主查询，内部查询称为子查询。

• Nested Query –
  In Nested Query,  Inner query runs first, and only once. Outer query is executed with result from Inner query.Hence, Inner query is used in execution of Outer query.
  Example –

  sql
Orders (OrderID, CustomerID, OrderDate);
Customers (CustomerID, CustomerName, ContactName, Country);

  查找已订购客户的详细信息。

  sql
SELECT * FROM Customers WHERE
CustomerID IN (SELECT CustomerID FROM Orders);




• Correlated Query –
  In Correlated Query,  Outer query executes first and for every Outer query row Inner query is executed. Hence, Inner query uses values from Outer query.
  Example –

  sql
Orders (OrderID, CustomerID, OrderDate);
Customers (CustomerID, CustomerName, ContactName, Country);

  查找已订购客户的详细信息。

  sql
SELECT * FROM Customers where
EXISTS (SELECT CustomerID FROM Orders
WHERE Orders.CustomerID=Customers.CustomerID);

连接操作和子查询的应用:
要了解嵌套子查询、相关子查询和连接操作之间的区别，首先我们必须了解在哪里使用子查询以及在哪里使用连接。

• When we want to get data from multiple tables we use join operation.
  Example: Let’s consider two relations as:

  sql
Employee (eId, eName, eSalary, dId);
Department (dId, dName, dLocation);

  现在，我们必须找到在伦敦工作的员工姓名和部门名称。在这里，我们必须显示员工表中的姓名和部门表中的姓名。因此，我们必须使用连接操作。

  sql
SELECT e.eName, d.dName from Employee e,
Department d
where e.dId=d.dId and d.dLocation="London";




• When we want to get data from one table and condition is based on another table we can either use Join or Subquery. Now, we have to find employee names working at London Location.

  这里，我们必须只显示雇员表中的 eName，因此我们可以使用连接操作或子查询
  使用连接操作–

  sql
SELECT e.eName from Employee e, Department d
where e.dId=d.dId and d.dLocation="London";

  使用子查询–

  sql
SELECT eName from Employee
where dId=(SELECT dId from Department where dLocation="London");

  了解了联接与子查询的基本区别之后，现在我们将了解嵌套子查询、相关子查询和联接操作之间的区别。

嵌套查询、关联查询和连接操作的区别: