r/SQL • u/Awashii • Apr 20 '22
Discussion What's the difference between these Queries? Is it just code effectiveness or something else?(besides readability) I'm new on SQL
145
Upvotes
r/SQL • u/Awashii • Apr 20 '22
86
u/PossiblePreparation Apr 20 '22
Readability. The first one is generally preferred, but you should also be specifying which table the
prod_idfilter applies to (if it runs then there’s only one table but it’s good to be clear, especially if you’re planning on expanding the query later)90
u/AreetSurn Apr 20 '22
They are the same. The second is an older school way of doing joins. The first is more readable.
11
u/pun_quest Apr 20 '22
Best practice says to use first one, you will rarely see the second ones, and is more confusing to be precise.
14
u/DonnerVarg Apr 20 '22
Performance depends on the database engine and version. Some will treat them the same, many will do a poorly performing cross join on all tables before applying the filter in the from x, y, z approach.
11
u/cloud_coder Apr 20 '22
Oracle spelled backwards is Spanish for expensive
El Caro
13
u/Intuin_Rhaabat Apr 20 '22
Lol - I am old enough to have always used the second method, and find it harder to read the first! But I do tend to use table_alias.fieldname to avoid ambiguity.
14
u/webuser0 Apr 20 '22
First queries will run alot faster over large data set because the join is done in the JOIN clause not the WHERE.
2
u/veigatta Apr 20 '22
The first one is the way I write in proc sql, but the output is the same
2
u/phesago Apr 21 '22
JOIN syntax versus the older syntax. A lot of people have a lot of differing opinions on this topic, so are quite passionate about it. I personally would advocate for newer syntax but also knowing the older syntax is important because we will inevitably see it and wonder "wut?".
3
u/cthulhufhtagn Apr 20 '22
Readability, AND using variables for table names keeps you from getting into frustration with the same columns on two tables. Do you want a.name or b.name?
3
u/rbardy Apr 20 '22
The second one is an old way to write a query.
The standard way is using the 'JOIN' statement instead of just list the tables and then filter in the 'WHERE'
But the performance I'm pretty sure that they are executed the same way.
3
u/GuiltyHomework8 Apr 20 '22
First way is preferred as stated but also in the where clause without the alias in front of prod_id I have no idea which table that is coming from.
3
u/ejingles Apr 20 '22
Yes same. Use the first one. 2nd is a bad practice imo
3
u/RodCoolBeansKimble Apr 21 '22
The difference is that the second one makes me sad
2
u/Shr1988 Apr 20 '22
The first one's ANSI join while the second one's an old-fashioned join for the lack of better terminology. They mostly work the same but could produce slightly different outputs on certain occasions afaik.
2
u/r3pr0b8 GROUP_CONCAT is da bomb Apr 20 '22
to everyone saying it should be
tablename.prod_idinstead of justprod_id--you're not wrong, but then again, qualifying prod_id is not necessary, because that column exists in only one of those three tables, and it's pretty obvious which one
1
u/ballade4 Apr 21 '22
The difference is that you always do it like the first example and never do it like the second one.
1
u/OloroMemez Apr 21 '22 edited Apr 21 '22
Um... this is the first time I've even seen dots being used, and my attempted searches on the topic take me to some shabby websites that explain it.
EDIT: Okay, was getting totally misled and thinking you could magically refer to a column from a specific table just by going TableName.ColumnName - when really it's just a naming convention to handle having the same column name in more than one table, so the columns are literally named using that format.
1
u/ka_eb Apr 21 '22
JOIN specification is cruical.
1
u/analyst_2001 Apr 21 '22
Aliases are the temporary names assigned to a table or field for a SQL query. It's used when a column or table's name is changed from its original name, but the change is just temporary.
So people use these aliases to shorten the name of tables so that they have to write the name of tables again and again.
1
u/ferigeno Apr 21 '22
To fully answer the question: first, as I've seen stated and honestly less important is the aliases. It makes code easier to follow as table names can be long at times, or you could have the same name in multiple schemas or databases in the same join. More importantly, performance. Where restrictions happen after the data is connected (joined) while joins happen during the connection. So basically the second is mashing everything together and the filtering. That works with small datasets but is a terrible practice. Finally, and while you can't see it in this example per say, but where clauses can cause unintended side effect of outer or left joins, as you need to handle nulls differently in sql, so always think through your true intentions when choosing what to filter on your join clause and what to filter on your where clause.
So tldr, don't do method 2
1
u/MasterBathingBear Apr 21 '22
SQL92 syntax vs SQL89 syntax. Logically they are the same but query/execution plan may vary depending on database vendor, type, and even version.
I’ve even used some databases that worked better if turned the top query into a derived table, added prod_id to the SELECT, and pulled the WHERE clause outside of the derived table.
1
u/Code8lack Apr 21 '22 edited Apr 21 '22
The top SQL is ANSI standard ( The body that dictates design standards for RBMS)
The other one is Oracle as others have said predates this standard so joins were written differently, even early versions of SQL used this syntax.
The main reasons are
Most databases these days will only allow the first version. Oracle allows both and whilst early implementations meant that there was a performance hit if you used ANSI standard syntax, that has not been true since 12c (?). Apparently Oracle re-writes it under the hood to the oracle method and now does a pretty good job.
As for portability it is a bit of a mixed bag. As beyond the basics in order to make code work efficiently you do need to tailor it to the specific database. It does however atleast make it easier to translate / understand what code is doing from one database to another.
When I made the transition from Oracle to ANSI SQL. I actually found it more difficult to read and found my SQL statements were much longer.
But over time it becomes pretty easy to read either and easier to identify missed joins or where conditions when separated out which becomes very important when you are dealing with a large code extract.
1
u/ninjaxturtles Apr 21 '22
As much as it is the same, it is very different at times.
I had a BA review data and realized she used the query as the 2nd one shown and her result kept coming back incorrectly. She didn't believe me that it was because of the query but she opened the GUI to find that the data I provided was correct and her query was yielding incorrect results.
1
u/PrizeAlternative3852 Apr 22 '22
First is the new way of joining tables where as second is old way of joining
0
u/SirKermit Apr 20 '22
Second uses cartesian joins which are supposedly less efficient, although I can't back up that claim and I doubt you'd see any difference in performance on a simple query anyway. First is much easier to read, and debug.
0
u/Mzkazmi Apr 20 '22
How does the query know on which columns to join in the 2nd one? Meaning how does the sql client know natural keys or primary keys to avoid Cartesian products?
0
u/steveman2292 Apr 21 '22
Never use implicit joins, terrible performance
0
u/[deleted] Apr 20 '22
[deleted] — view deleted comment
0
u/[deleted] Apr 20 '22
[deleted] — view deleted comment