从 SQL Server 中的两个表中选择没有公共字段的正确方法来加入 [英] Correct way to select from two tables in SQL Server with no common field to join on

问题描述

在过去，我曾经写过这样的选择语句:

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'

但是有人告诉我，在FROM"子句中使用逗号分隔的表名与 ANSI92 不兼容.应该总是有一个 JOIN 语句.

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 子句中使用逗号分隔的表名的传统"方法(参见代码示例)，那么它工作得非常好.如果这种方法被认为是错误的或不好的做法，我会感到不舒服.

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?

额外信息:

表 1 包含地理"数据类型的位置列表表 2 包含不同的地理"位置列表

Table1 contains a list of locations in 'geography' data type Table2 contains a different list of 'geography' locations

我正在编写 select 语句来比较位置之间的距离.据我所知你不能在地理专栏上做 JOIN??

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

推荐答案

您可以(应该)使用 CROSS JOIN.以下查询将等同于您的:

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'

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

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

FROM table1 
INNER JOIN table2 ON 1=1

这篇关于从 SQL Server 中的两个表中选择没有公共字段的正确方法来加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！