Correct way to select from two tables in SQL Server with no common field to join on

sql, sql-server, sql-server-2012

Solution

You can (should) use `CROSS JOIN`. Following query will be equivalent to yours:

SELECT 
   table1.columnA
 , table2.columnA
FROM table1 
CROSS JOIN table2
WHERE table1.columnA = 'Some value'

or you can even use INNER JOIN with some always true conditon:

FROM table1 
INNER JOIN table2 ON 1=1

Problem

Back in the old days, I used to write select statements like this: ``` SELECT table1.columnA, table2.columnA FROM table1, table2 WHERE table1.columnA = 'Some value' ``` However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement. This leads to my problem.... I want to do a comparison of data between two tables but there is no common field in both tables with which to create a join. If I use the 'legacy' method of comma separated table names in the FROM clause (see code example), then it works perfectly fine. I feel uncomfortable using this method if it is considered wrong or bad practice. Anyone know what to do in this situation? Extra Info: Table1 contains a list of locations in 'geography' data type Table2 contains a different list of 'geography' locations I am writing select statement to compare the distances between the locations. As far I know you cant do a JOIN on a geography column??

Original source