CODEWITHSUNDEEP
sqldatabasejoin
shinryu333
shinryu333

Reputation: 333

Rewrite SQL Code Using a Join Instead of Subquery

I'm having trouble visualizing how subqueries would look in the form of joins instead. In particular, the following SQL:

SELECT DISTINCT e.EmployeeID, e.LastName, e.FirstName
FROM Employees e 
INNER JOIN Orders o ON e.EmployeeID = o.EmployeeID
WHERE EXISTS
(
  SELECT c.Country
  FROM Customers c
  WHERE c.Country = e.Country
)

It would greatly be appreciated if I could receive some tips on what to do when I want to turn a statement with a subquery into a join instead.

Upvotes: 0

Views: 84

Answers (2)

wildplasser
wildplasser

Reputation: 44250

This looks clean. In fact, it is not:

SELECT DISTINCT e.EmployeeID, e.LastName, e.FirstName
FROM Employees e
JOIN Orders o ON o.EmployeeID = e.EmployeeID
JOIN Customers c ON c.Country = e.Country
        ;

The point is that if there are more than one matching rows for orders and Customers, these will all cause a seperate copy of the Employee record to be merged. These will later have to be suppressed by theDISTINCT(the optimiser might catch this, or it might not)

The point is: you dont need a distinct, since you are only selecting columns from employee:

SELECT e.EmployeeID, e.LastName, e.FirstName
FROM Employees e 
WHERE EXISTS( 
  SELECT * FROM Orders o
  WHERE o.EmployeeID = e.EmployeeID
  )
AND EXISTS(
  SELECT * FROM Customers c
  WHERE c.Country = e.Country
  );

Or,in a more or less ugly style:

SELECT e.EmployeeID, e.LastName, e.FirstName
FROM Employees e 
WHERE EXISTS(
  SELECT c.Country
  FROM Orders o
  JOIN Customers c
  ON o.EmployeeID = e.EmployeeID
  AND c.Country = e.Country
);

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You need to be careful about duplicates, but the transformation for EXISTS is quite direct:

SELECT DISTINCT e.EmployeeID, e.LastName, e.FirstName
FROM Employees e INNER JOIN
     Orders o
     ON e.EmployeeID = o.EmployeeID INNER JOIN
     (SELECT DISTINCT c.Country
      FROM Customers c
     ) c
     ON  c.Country = e.Country

Upvotes: 2

Related Questions