Which SQL operation will give me the product of two tuples?
Refresh my memory. I can't recall how to join tuples (a,b) and (c) to produce (a,b)*(c). For example:
n
---
0
1
2
And
site_id value
----------- -------------
1 a
1 b
2 c
I'd like to end up with:
site_id value n
----------- -------------- --
1 a 0
1 a 1
1 a 2
1 b 0
1 b 1
1 b 2
2 开发者_如何学Python c 0
2 c 1
2 c 2
How can I achieve this?
That's called a CROSS JOIN, also known as the Cartesian product.
SELECT *
FROM Table1
CROSS JOIN Table2
You can also do it without the JOIN keyword just by using a comma:
SELECT * FROM Table1, Table2
Here's full test code you can use to verify that it works:
CREATE TABLE Table1 (n int NOT NULL);
INSERT INTO Table1 (n) VALUES
(0),
(1),
(2);
CREATE TABLE Table2 (site_id int NOT NULL, value nvarchar(100) NOT NULL);
INSERT INTO Table2 (site_id, value) VALUES
(1, 'a'),
(1, 'b'),
(2, 'c');
SELECT Table2.site_id, Table2.value, Table1.n FROM Table1, Table2
Results:
site_id value n
1 a 0
1 a 1
1 a 2
1 b 0
1 b 1
1 b 2
2 c 0
2 c 1
2 c 2
Do a CROSS JOIN
You can try
Select *
FROM table1, table2
精彩评论