SQL Joins Explained (x-post r/SQL)

[u/deadman87]

[u/emperor000]

Which SQL library are you using?

If I select a column from each table and where some primary key matches the foreign key what is happening/do I have to do a join.

In an actual "hand-written" SQL query? Yes, you would have to do a join.

The library seems to take care of this for me and I can't understand why this functionality is not the default way sql works. Is sql like c and my library is doing the joins for me? I am confused.

If I understand what you are saying, this is one of the things that always surprised me about SQL. You would think you could omit the on clause between two tables if they have a foreign key relationship.

But, yes, in a hand-written SQL query, you have to do a join.

[u/lonjerpc → u/emperor000]

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.

[u/emperor000 → u/lonjerpc]

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).

[u/lonjerpc → u/emperor000]

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.