As a fairly experienced dev, I will note that this is VERY IMPORTANT for anyone who may ever touch a database, even if it's just via an ORM (this is what it translates to on the back end, and the concepts are the same). I'm surprised how little understanding of SQL there is when I interview folks. My main note would be to mention which database system you're using, because the SQL changes slightly by system (sometimes concatenation is +, inner join needs to be explicit, etc.).
As for your questions:
It's important more for the scale of WHAT you want back (do you want an exact match, or do you want nulls if one table doesn't have a match?). Note that any sort of outer join will return more records.
The efficiency will depend on the system you're using and how it parses, what is indexed and what isn't, etc. Figure out what you need first, and optimize from there.
Again, this depends on what you're doing. A lot of joins do slow database queries down, especially if some of the keys aren't indexed. It may also make it harder to read if you have a long set of joins at once.
That said, it can also get hard to read (and much slower) if you declare a bunch of temp tables and keep putting partial results in there.
If you have a bunch of related data that is separated by the normalization, and you're just trying to traverse the tree (which is really all a relational database is), then you are generally better off bundling into one query. Retrieving it that way makes sense because you want a combined dataset, and generally things are tied via foreign keys. If you have to bundle disparate data sources (i.e. people vs. other entities), format them to match in a more generic format, etc. then consider breaking up the query into smaller parts.
Hi, Unfrozen Caveman Dev.
Thank you for your comment.
To your questions:
What I wanted back? - As much information as possible about all employees. This, basically, made me think of LEFT JOIN to employees table, so that I don't miss anyone in this list.
I was using Oracle SQL developer to access my database.
Makes sense. Then yes, you used the correct join. And it's worth noting that JOIN syntax is slightly different in different systems, so it's worth specifying a little more clearly which join you're using.
INNER JOIN instead of JOIN, LEFT OUTER JOIN, etc. I can't remember, but I think those work in Oracle just fine, and will work across a few other systems.
If I'm wrong, no worries, just a readability thing.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
As a fairly experienced dev, I will note that this is VERY IMPORTANT for anyone who may ever touch a database, even if it's just via an ORM (this is what it translates to on the back end, and the concepts are the same). I'm surprised how little understanding of SQL there is when I interview folks. My main note would be to mention which database system you're using, because the SQL changes slightly by system (sometimes concatenation is +, inner join needs to be explicit, etc.).
As for your questions:
The efficiency will depend on the system you're using and how it parses, what is indexed and what isn't, etc. Figure out what you need first, and optimize from there.
That said, it can also get hard to read (and much slower) if you declare a bunch of temp tables and keep putting partial results in there.
If you have a bunch of related data that is separated by the normalization, and you're just trying to traverse the tree (which is really all a relational database is), then you are generally better off bundling into one query. Retrieving it that way makes sense because you want a combined dataset, and generally things are tied via foreign keys. If you have to bundle disparate data sources (i.e. people vs. other entities), format them to match in a more generic format, etc. then consider breaking up the query into smaller parts.
Hi, Unfrozen Caveman Dev.
Thank you for your comment.
To your questions:
I appreciate your feedback.
Makes sense. Then yes, you used the correct join. And it's worth noting that JOIN syntax is slightly different in different systems, so it's worth specifying a little more clearly which join you're using.
INNER JOIN instead of JOIN, LEFT OUTER JOIN, etc. I can't remember, but I think those work in Oracle just fine, and will work across a few other systems.
If I'm wrong, no worries, just a readability thing.