31

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??

volume one
  • 6,800
  • 13
  • 67
  • 146
  • how would you compare if there is no common field? – RoMEoMusTDiE Jul 10 '13 at 22:58
  • 3
    `Table1, Table2` is functionally identical to `Table1 CROSS JOIN Table2`, is that ***really*** what you are trying to achieve? If one table has more than one row, the rows from both tables will be duplicated... You should probably give some example input data and the corresponding example output data. – MatBailie Jul 10 '13 at 23:16
  • Yes MatBaile, I need to use a Cross Join. Thank you for you help – volume one Jul 11 '13 at 00:24

3 Answers3

54

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
Jim Geurts
  • 20,189
  • 23
  • 95
  • 116
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
1

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
Zeeshan
  • 2,884
  • 3
  • 28
  • 47
ha9u63a7
  • 6,233
  • 16
  • 73
  • 108
1

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.

vinay koul
  • 348
  • 1
  • 9