How to Join Two Tables Without Using Join Keywords? – Interview Question of the Week #266

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)