两张完全没有关系的表怎么查询_Sql Server

两张完全没有关系的表如何查询
我有个 COMPANY表和其他两个表INV_SUB和INV_MAIN一点对应关系也没有的，我先有关系的INV_MAIN INV_SUB表联合起来查没有重复记录

SELECT a.list_no, a.prod_no, a.batch_no, a.inv_num, a.sell_price, e.cli_no, e.cli_name, e.cli_add, e.cli_tel, e.inv_date
FROM dbo.inv_sub AS a LEFT OUTER JOIN
(SELECT dbo.inv_main.list_no, dbo.inv_main.cli_no, dbo.client.cli_name, dbo.client.cli_add, dbo.client.cli_tel, dbo.inv_main.inv_date
FROM dbo.inv_main LEFT OUTER JOIN
dbo.client ON dbo.inv_main.cli_no = dbo.client.cli_no) AS e ON a.list_no = e.list_no

加上COMPANY表以后就发现有些记录重复了,我的COMPANY表只有一条记录，我想来想去也不明白为什么会查出的记录会重复

SELECT a.list_no, a.prod_no, a.batch_no, a.inv_num, a.sell_price,b.cli_no as md_no, e.cli_no, e.cli_name, e.cli_add, e.cli_tel, e.inv_date
FROM (select top 1 cli_no company) b, dbo.inv_sub AS a LEFT OUTER JOIN
(SELECT dbo.inv_main.list_no, dbo.inv_main.cli_no, dbo.client.cli_name, dbo.client.cli_add, dbo.client.cli_tel, dbo.inv_main.inv_date
FROM dbo.inv_main LEFT OUTER JOIN
dbo.client ON dbo.inv_main.cli_no = dbo.client.cli_no) AS e ON a.list_no = e.list_no

------解决方案--------------------
没关关系的表怎么会关联？除非cross join 。但是这个开销将非常巨大。如果出现这个情况，你应该首先怀疑设计是否有问题，而不是去在思考怎么关联。
------解决方案--------------------
有种方法好像可以使用表中的一种连接方式就表连接起来
------解决方案--------------------

SQL code

select top 1 cli_no company
------解决方案--------------------
SELECT a.list_no, a.prod_no, a.batch_no, a.inv_num, a.sell_price,b.cli_no as md_no, e.cli_no, e.cli_name, e.cli_add, e.cli_tel, e.inv_date
FROM (select top 1 cli_no company) b, dbo.inv_sub AS a LEFT OUTER JOIN
 (SELECT dbo.inv_main.list_no, dbo.inv_main.cli_no, dbo.client.cli_name, dbo.client.cli_add, dbo.client.cli_tel, dbo.inv_main.inv_date
 FROM dbo.inv_main LEFT OUTER JOIN
 dbo.client ON dbo.inv_main.cli_no = dbo.client.cli_no) AS e ON a.list_no = e.list_no


楼主为何既 from a,b 又 from a left join b 的。

SELECT a.list_no, a.prod_no, a.batch_no, a.inv_num, a.sell_price,b.cli_no as md_no, e.cli_no, e.cli_name, e.cli_add, e.cli_tel, e.inv_date
FROM (select top 1 cli_no from company) b cross join dbo.inv_sub AS a LEFT OUTER JOIN
 (SELECT dbo.inv_main.list_no, dbo.inv_main.cli_no, dbo.client.cli_name, dbo.client.cli_add, dbo.client.cli_tel, dbo.inv_main.inv_date
 FROM dbo.inv_main LEFT OUTER JOIN
 dbo.client ON dbo.inv_main.cli_no = dbo.client.cli_no) AS e ON a.list_no = e.list_no

试试。
------解决方案--------------------
SELECT a.list_no, a.prod_no, a.batch_no, a.inv_num, a.sell_price,b.cli_no as md_no, e.cli_no, e.cli_name, e.cli_add, e.cli_tel, e.inv_date
FROM (select top 1 cli_no company) b cross join dbo.inv_sub AS a LEFT OUTER JOIN
 (SELECT dbo.inv_main.list_no, dbo.inv_main.cli_no, dbo.client.cli_name, dbo.client.cli_add, dbo.client.cli_tel, dbo.inv_main.inv_date
 FROM dbo.inv_main LEFT OUTER JOIN
 dbo.client ON dbo.inv_main.cli_no = dbo.client.cli_no) AS e ON a.list_no = e.list_no
------解决方案--------------------
其实原因很简单，单看这里FROM (select top 1 cli_no company) b, dbo.inv_sub AS a 就能发现，由于后面没有on子句，因此这里其实执行的是cross join，换句话说，由于你采用以前的写法select * from table1,table2如果后面没有on子句，SQL就自动按照cross join来执行了。
为了更好的说明问题，可以参考下面的代码：
SQL codeCREATE TABLE t1(    id INT)INSERT INTO t1SELECT 1CREATE TABLE t2(    id INT)INSERT INTO t2SELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5CREATE TABLE t3(    id INT)INSERT INTO t3SELECT 3 UNION ALLSELECT 5SELECT * FROM t1SELECT * FROM t2SELECT * FROM t3SELECT * FROM t1,t2SELECT * FROM t1, t2 AS a LEFT JOIN t3 AS b ON a.id=b.id