I have the following query:
select *
FROM y,x
y has 3 tuples and x has 3 tuples.
Is the number of tuples returned by the query 9 since there is no condition?
Asked
Active
Viewed 364 times
0
-
7yes..it results in a cross join or in terms of sets a cartesian product. – Vamsi Prabhala Jan 23 '17 at 00:59
-
See marcothesane's answer on this. You shouldn't even be using this 1980s join syntax anymore for being more prone to errors and most often harder to read and maintain. In your example for instance: who could say whether a cross join was actually intended or the join criteria simply forgotton or mistakenly deleted? Use explicit ANSI joins. – Thorsten Kettner Jan 23 '17 at 09:40
-
And with "tuples" you mean records / rows. Correct? – Thorsten Kettner Jan 23 '17 at 09:42
3 Answers
1
And I'd like to add:
Since the early nineties, the ANSI standard for SQL offers the explicit join.
Instead of :
SELECT *
FROM x,y;
You should do it explicitly:
SELECT *
FROM x
CROSS JOIN x;
This way, you document that you don't just happen to join two tables without caring how, but you CROSS JOIN the two tables and know what you're doing.
Not necessary to delve into the topic why cross joins should be used with care ...
Good luck -
Marco the sane
marcothesane
- 6,192
- 1
- 11
- 21
0
A join links two tables and selects tuples by join type(left, right, full.) In your example it looks like you're looking for a function similar to this.
SELECT *
FROM X
JOIN Y
ON x.column_name=y.column_name;
You need to have a related field in each table to join them. The output would actually be 6 tuples.
Luke Shinn
- 346
- 5
- 12
0
They are simply the Cartesian products
Table A = {1,2}; Table B = {3,4}
Table A × Table B = {1,2} × {3,4} = {(1,3), (1,4), (2,3), (2,4)}
Table B × Table A = {3,4} × {1,2} = {(3,1), (3,2), (4,1), (4,2)}
http://www.sql-join.com/sql-join-types/
Also check What is the difference between Cartesian product and cross join?
