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?

How to Join Two Tables Without Using Join Keywords? - Interview Question of the Week #266 TwoTables-800x480

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.

Solarwinds

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
How to Forecast the Size of Restore of Backup in SQL Server? – Interview Question of the Week #265
Next Post
How to Do Case Insensitive Search? – Interview Question of the Week #267

Related Posts

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.

    Reply
  • Would cross apply be acceptable for this question?

    Reply
  • Jeremy J. Giaco
    March 2, 2020 5:48 am

    typo: “it is not preferred ANSI standard… ” “syntax”, not “index”. fyi

    Reply
  • Justin Swanhart
    March 2, 2020 5:34 pm

    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)

    Reply
  • 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.

    Reply
  • 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.

    Reply

Leave a Reply

Menu