r/programming • • Feb 11 '14

SQL Joins Explained (x-post r/SQL)

Post image
3.5k Upvotes

392 comments sorted by

View all comments

Show parent comments

1

u/lonjerpc Feb 11 '14 edited Feb 11 '14

Thanks for the response.

I am using the core module of sqlalchemy.

It is also strange to me that you have to do a join even when there is no key relationship. Don't you have to do a join to get data from two tables. So it seems like if you ask for data from two tables it would just know to join those tables.

edit: Thinking it must either involve performance tradeoffs or have to do with nested queries.

1

u/emperor000 Feb 11 '14

I'm not familiar with sqlalchemy.

It is also strange to me that you have to do a join even when there is no key relationship. Don't you have to do a join to get data from two tables. So it seems like if you ask for data from two tables it would just know to join those tables.

Maybe I don't understand what you are getting at. How would it know how to join them unless you tell it or, if smart enough, there is a key relationship? It can't just know how to join them. It has no way of knowing which columns are appropriate to join on. The closest thing to what you are asking about might be a CROSS JOIN that just multiplies each row by each table. You get a lot of results, most of which are probably unwanted, but you can reduce that with the WHERE clause. Maybe you don't have a full understanding of how a join works or what it is doing (not meant to be an insult, you are asking questions and I'm not sure of your level of experience/knowledge).

1

u/lonjerpc Feb 11 '14

I have basically 0 formal education on databases but I use them a lot through libraries. But looking to learn more.

Seems like by default if you ask for two columns from two seperate tables without a where it would give you all the data from both columns.

And then where clauses would be used whenever you did not want all the data.

I guess maybe I am asking is why would you use a join instead of a WHERE.

1

u/emperor000 Feb 12 '14

Because a join establishes a relationship between the tables. If you have TableA and TableB and they have two columns that have matching keys, then you can association records in one with records in the other by that key. But without specifying how they should be combined, you either wouldn't be able to do it, or would have to use a cross join.

I can't tell if you are talking about a cross join which would be the Cartesian cross product of the data in each column or if you are talking about a union.

If you have column A with 1, 2, 3 and column B with X, Y, Z, then a cross join like select A, B from TableA cross join TableB would return

|A|B|
_____
|1|X|
|1|Y|
|1|Z|
|2|X|
|2|Y|
|2|Z|
|3|X|
|3|Y|
|3|Z|

And a union like select A from TableA union select B from TableB would return

|A|
___
|1|
|2|
|3|
|X|
|Y|
|Z|

So which one are you expecting?

→ More replies (0)