1

I have the following code to retrieve and display the first and last names of writers who are also editors (ED_ID = WRT_ID).

SELECT Writers.WRT_LastName AS LastName, Writers.WRT_FirstName AS FirstName
FROM Writers INNER JOIN Editors ON Editors.ED_ID = Writers.WRT_ID;

It produces the results I want, but how would I write it using a subquery and produce the same results?

I am using Access 2013

Let me know if I need to provide more info.

ddrossi93
  • 385
  • 2
  • 7
  • 19

2 Answers2

1

You just need to put the join conditions into a where clause subquery:

SELECT WRT_LastName as LastName, WRT_FirstName as FirstName
FROM   WRITERS
WHERE  WRT_ID in (SELECT ED_ID FROM EDITORS)
Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • That worked. Thank you. Is that how it always works when replacing JOINs with subqueries? – ddrossi93 Dec 02 '15 at 22:47
  • Yes exactly. You just have to match up the logic. In the join query, you are using the join to filter out WRT_IDs from the WRITERS table that don't have a matching ED_ID in the EDITORS table. Transfer that same logic to the where clause and you have the subquery version. – Aaron Dietz Dec 02 '15 at 23:03
  • Awesome. Thanks a lot. – ddrossi93 Dec 02 '15 at 23:04
  • This is a pretty good read http://stackoverflow.com/questions/2577174/join-vs-sub-query it touches on what Gordon mentioned above (subqueries will naturally filter out duplicates, joins will not), and has some interesting info about performance pros and cons. – Aaron Dietz Dec 02 '15 at 23:09
1

Presumably, you don't want duplicate results. I point that out, because the subquery is going to be different from the join if the Editors table has duplicates.

Typical ways of writing this are using IN or EXISTS. I tend to go with the latter, although the two are usually pretty equivalent performance wise (on the other hand, NOT EXISTS is preferable to NOT IN semantically).

SELECT Writers.WRT_LastName AS LastName, Writers.WRT_FirstName AS FirstName
FROM Writers 
WHERE EXISTS (SELECT 1 FROM Editors WHERE Editors.ED_ID = Writers.WRT_ID);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So if one if the tables were to have duplicates, I should use EXISTS rather than IN? – ddrossi93 Dec 02 '15 at 22:49
  • and what does SELECT 1 do in the subquery? – ddrossi93 Dec 02 '15 at 22:50
  • @Ddrossi93 . . . `EXISTS` just checks for the existence of a row. The `1` is arbitrary. `EXISTS` and `IN` do the same thing. The `JOIN` can produce duplicates if `Editors` has duplicate writers in it. The conditions in the `WHERE` clause will not duplicate the results, so the result sets are not always identical. – Gordon Linoff Dec 02 '15 at 22:57