CODEWITHSUNDEEP
mysqlsql
Doug Smith
Doug Smith

Reputation: 29316

If I natural join these two tables, why is the result what it is? (Basically, explanation please.)

A B   C D
1 3   3 3
3 3

The result is apparently, after natural joining

A B C D
1 3 3 3
3 3 3 3

Why is this? I thought natural join required there to be a similar attribute in the two cases? There's not even a C or D attribute in the first table, so how could they join?

For instance, our prof said these two join because for the first resulting row, the C's are the same, and the same for the second.

A B C    C D
1 2 3    3 1
4 5 6    6 2
7 8 9

Or do they just merge normally? If there was a 2 1 after 6 2 in the second table there, how would the merge appear?

Upvotes: 3

Views: 179

Answers (3)

davepmiller
davepmiller

Reputation: 2708

If there exists a similar column name, it will merge them. But a natural join will always create a column for every unique column name.

Good information/examples on joins here. http://www.w3resource.com/sql/joins/

Upvotes: 0

Mike Parkhill
Mike Parkhill

Reputation: 5551

In your first example there is no common field to join on so it's simply returning all the possible combinations of both tables.

Since your second set has a common field then the result set would be filtered to:

A B C D
1 2 3 1
4 5 6 2

Upvotes: 1

Sean Vieira
Sean Vieira

Reputation: 159975

Cross joins do not require any sort of matching condition - every row in Set A is matched with every row in Set B.

Other types of joins (INNER, OUTER, LEFT, RIGHT) all match on a condition and so are generally more limited (i.e. "only join a row of Set A with a row of Set B iff (if and only if) the value in the C column of Set A matches the value in the C column in Set B").

However, you can make such joins effectively CROSS joins by simply making the condition always be true:

SELECT *
FROM SetA
LEFT INNER JOIN SetB
    ON 1 = 1  -- Voila, instant cross join

Upvotes: 1

Related Questions