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

Question

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

User · Answer

A suggestion - when using cross join please take care of the duplicate scenarios. For example in your case:

Table 1 may have >1 columns as part of primary keys(say table1_id, id2, id3, table2_id)
Table 2 may have >1 columns as part of primary keys(say table2_id, id3, id4)

since there are common keys between these two tables (i.e. foreign keys in one/other) - we will end up with duplicate results. hence using the following form is good:

WITH data_mined_table (col1, col2, col3, etc....) AS
SELECT DISTINCT col1, col2, col3, blabla
FROM table_1 (NOLOCK), table_2(NOLOCK))
SELECT * from data_mined WHERE data_mined_table.col1 = :my_param_value

User · Answer

Cross join will help to join multiple tables with no common fields But be careful while joining as this join will give cartesian resultset of two tables  QUERY   SELECT     table1 columnA    table2 columnA FROM table1  CROSS JOIN table2   Alternative way to join on some condition that is always true like  SELECT     table1 columnA    table2 columnA FROM table1  INNER JOIN table2 ON 1 1   But this type of query should be avoided for performance as well as coding standards

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2012