正确的方法从SQL Server中的两个表中选择没有要加入的公共字段

Back in the old days, I used to write select statements like this:

回到过去，我曾经写过这样的select语句：

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.

但是我被告知在“FROM”子句中使用逗号分隔的表名称与ANSI92不兼容。应始终有一个JOIN语句。

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.

这导致了我的问题....我想对两个表之间的数据进行比较，但两个表中没有用于创建连接的公共字段。如果我在FROM子句中使用逗号分隔的表名的'legacy'方法（参见代码示例），那么它的工作完全正常。如果被认为是错误或不好的做法，我会觉得使用这种方法很不舒服。

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

表1包含“地理”数据类型中的位置列表。表2包含不同的“地理位置”列表

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

我正在写select语句来比较位置之间的距离。到目前为止，我知道你不能在地理专栏上加入？

3 个解决方案

#1

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

你可以（应该）使用CROSS JOIN。以下查询将等同于您的：

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:

或者你甚至可以使用INNER JOIN和一些总是真实的条件：

FROM table1 
INNER JOIN table2 ON 1=1

#2

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.

但是应该避免这种类型的查询以获得性能以及编码标准。

#3

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)
表1可能有> 1列作为主键的一部分（比如table1_id，id2，id3，table2_id）
Table 2 may have >1 columns as part of primary keys(say table2_id, id3, id4)
表2可能有> 1列作为主键的一部分（比如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

#1