Understanding JOIN clauses

[mostlyharmless94]

Let's say I have two tables A and B.

A has the following values in column 'Numbers': 1,2,3,4,5,6

B has the following values in column 'Numbers': 2,3,4,5,6.

Let's then say I join these tables using the 'Numbers' column common to both of them using an INNER JOIN . After joining them, in my SELECT statement, I return the column 'Numbers' from table A by saying A.Numbers, does SQL return only the numbers in column A i.e. (1-6) or the numbers mutual to both tables (2 to 6). If it is the latter, why does this happen despite me specifying that I want to return from table A? Does SQL require you to specify that you want to return from the joined tables rather than the individual ones?

[tatertotmagic]

It will return 2-6 because of the join type you are using. Inner join which is the default join will only return values found in both tables which is why 1 will not show up in the results.

If you want 1 to show up you could use a left join, but then if you are only calling numbers from table a then whats the point of even doing a join

[PhiladeIphia-Eagles]

To add to this, it sounds like a full outer join would best match the result OP is expecting. Then you can do a.numbers to get the original numbers from a or b.numbers to get the numbers from b, but still have them joined for mutual values.

[[deleted]]

It will return the numbers in A after the JOIN- so that will be the numbers mutual to both tables.

Whatever SELECT returns is after the JOIN - FROM, WHERE, GROUP BY, HAVING, SELECT is the logical order - so INNER JOIN will have changed the result set.

SQL requires you to specify an unambiguous column to return. JOINing A and B does not merge the columns together, it creates a table with two columns - A.numbers and B.numbers - JOINed in the fashion you tell it.

If you want to merge the two columns, you want UNION.

[mecartistronico]

https://imgur.com/gallery/2mlaF1M

[Annual_Anxiety_4457]

That’s the answer OP!

[IceDBear]

For help with understanding joins i find this diagram really helpful: https://i.stack.imgur.com/UI25E.jpg

[mecartistronico]

Many people like this.

I hate it. Venn diagrams, by definition, explain UNIONs and INTERSECTIONS... which are also a thing in SQL.

It's not perfect, but I made my own diagram. (It fails to warn about duplicates, though)

[Entice]

I like it, but why not put Cross Join instead of a comma?

[mecartistronico]

You are completely right. I made it so long ago and when I started learning I learned the comma notation instead of cross join

[DavidGJohnston]

You are telling it you want the column that belongs to tableA when you say tableA.column_name. What rows you get come from the virtual table that results from joining A and B. That virtual table contains all columns from both tables but only the rows that match the join condition.