Question: Can you join two tables without writing the JOIN keyword? Yes. A comma-separated table list forms a cross product; a matching predicate in WHERE can express an inner join.
I’ve sat on many interview panels. I wouldn’t choose this question merely to test whether a candidate remembers an old syntax. I’d rather ask what rows the query produces, and what happens when someone forgets the condition.
The original example used Sales.Invoices and Purchasing.PurchaseOrders in WideWorldImporters without a predicate. That returns every invoice paired with every purchase order, regardless of any business relationship. Large tables can produce an enormous result. Here is the same lesson with two tiny tables:
DECLARE @TableA table (ID int NOT NULL);
DECLARE @TableB table (ID int NOT NULL);
INSERT @TableA VALUES (1), (2);
INSERT @TableB VALUES (2), (3);
SELECT a.ID AS A_ID, b.ID AS B_ID
FROM @TableA AS a, @TableB AS b
ORDER BY a.ID, b.ID;
SELECT a.ID AS A_ID, b.ID AS B_ID
FROM @TableA AS a, @TableB AS b
WHERE a.ID = b.ID;
SELECT a.ID AS A_ID, b.ID AS B_ID
FROM @TableA AS a
INNER JOIN @TableB AS b ON a.ID = b.ID;The first query returns four pairs: (1,2), (1,3), (2,2) and (2,3). The second returns only (2,2). The third writes that same matching operation with an explicit INNER JOIN.
The comma syntax is accepted SQL, but I prefer an explicit join in application code. It separates the relationship between tables from other filters and makes an accidental cross product easier to spot. Equivalent inner-join forms can receive the same execution plan; punctuation alone isn’t a performance improvement.
Don’t extend this equivalence blindly to outer joins. A predicate in WHERE can remove the unmatched rows that a left join was meant to preserve.
For more examples, see the introduction to joins, INNER JOIN versus JOIN, INNER JOIN versus a WHERE condition and a self-join example.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
8 Comments. Leave new
Great blurb, but it doesn’t work across all implementations of SQL. The first time I saw this syntax was when someone I worked with was having problems executing his script on Oracle. I immediately pointed to this as the issue and we got his query to run using the JOIN keyword.
I think it’s lame for interviewers to ask about totally obscure syntax like this, especially when it’s not applicable to all flavors of SQL. It’s trivia, not something that has real-world benefit of knowing.
Absolutely and totally agree with your note.
Would cross apply be acceptable for this question?
typo: “it is not preferred ANSI standard… ” “syntax”, not “index”. fyi
Thanks, I fixed it after reading your note.
That is because it predates the SQL standard. Lol. The standard was required for OUTER JOIN without really weird syntax:
Select * from a, b where a.id = b.id (+)
instead of
Select * from a left join b using (id)
You showed a Cartesian product, and it’s the worst way to join two tables, because it requires a lot of memory to get the result.
Wait! I got the answer. I didn’t read the article. Comma separated table list and “join on” the where clause. Like the old schoolers. And hope the database can query plan accordingly. Because this is like stupidest way to express a desire to the database. It’s not 1990.