Question: How to Join Two Tables Without Using Join Keywords?
Answer: In my life, I have attended many interviews as an interviewer. Sometimes the interview panel is asking progressive positive questions and sometimes they ask a question which is regressive. Today’s question is such question, I really would have never asked in the interview personally, as it just checking if the candidate knows one particular syntax or not.
Yes, it is possible to join two tables without using the join keyword. Here is how you can do it.
SELECT * FROM [Sales].[Invoices],[Purchasing].[PurchaseOrders]
The result of the above query will be cross join between the two tables which are mentioned in the query. Not only that you can also put multiple tables (more than 2) in the FROM clause with a comma between them and they will be all cross joined. Cross join is also known as cartesian join.
If we specify the WHERE condition to the join which we just have seen, we can also convert the same cross join to inner join as well. Here is how you can join two tables which have a relation between them.
SELECT * FROM TableA a, TableB b WHERE a.condition1 = b.condition2
Once again, please note that while the syntax above works great and still produces similar efficient results for Inner Join and Cross Join, it is not preferred ANSI standard syntax. It is suggested that for clarity of the code and to follow the standard, it is a good idea to use the explicit JOIN Keywords.
Here are a few additional blog posts on the same subject, which you may find useful.
- SQL SERVER – Introduction to JOINs – Basic of JOINs
- SQL – Difference Between INNER JOIN and JOIN
- SQL SERVER – What is the Difference Between An INNER JOIN and WHERE Clause
- What is a Self Join? Explain with Example – Interview Question of the Week #064
Reference: Pinal Dave (https://blog.sqlauthority.com)
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.