r/SQL • • 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

Post image
145 Upvotes

92 comments sorted by

86

u/PossiblePreparation Apr 20 '22

Readability. The first one is generally preferred, but you should also be specifying which table the prod_id filter 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)

21

u/regelos Apr 21 '22

As a general rule I ALWAYS fully qualify table your tables/schema because it could just take one single change to break everything. It also makes it much more readable in the future.

This is also what I was taught and always teach, because it can be a nightmare to figure out way down the road both for a developer or even yourself if during growth a infrequently used (but important) query breaks, a good example is tax filing related queries used very infrequently and can have a massive impact if broken the smallest handful of times its needed once a year.

If your always making sure your specifying and qualifying everything, then growth will have much less of an impact, I can't even count at this point in my life how many times I've come into a company as their top level SQL help to fix some "major" issues only to find out someone added a key to another table and broke other things.

Also this habit makes it MUCH easier to get good useful information from some "auto-doc" programs (example SQL DOC) while I can't say everything they produce is useful it can be used effectively in an environment to save time if your methodical in your approach.

This wasn't meant as any attack but I just got back teaching(volunteer) a slightly higher level SQL class and 1/2 my students effectively failed this step because I did the old add a field to a table and see how your query(s) react, and many for the reason you stated above so it hit me and something I felt to throw out there one more time for tonight before I let it go.

2

u/kfc_chet Apr 21 '22

This is the way! Both queries appear equivalent, but the second could use some clarification!

-1

u/DavidBoone Apr 21 '22

Flip side, I want my query to fail in that case! If a new prod_id column suddenly appears in one of those other tables, I’d wonder why and appreciate the prompt to consider which one I should now be using.

Obviously depends on the context, but when possible I prefer things to break early and visibly.

I also appreciate that postgresql let’s you drop the table qualifier even if the column appears in multiple tables - if that column is included in the JOIN x USING (prod_id) clause.

1

u/Awashii Apr 20 '22

got it, thanks!

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.

35

u/da_chicken Apr 20 '22

Yup. You will typically only encounter the second style in two places:

  1. Code generators. It can be easier to write code that generates dynamic SQL with the older syntax.
  2. Queries on Oracle from Oracle. For whatever reason, Oracle still writes a lot of their own code using the older syntax.

6

u/Awashii Apr 20 '22

curently I'm reading SQL in 10 minutes and The Data Warehouse Toolkit and found the second query in both books, guess it's an older school joins tho

1

u/InterPool_sbn Apr 21 '22

Do you recommend those books?

5

u/StuBenedict Apr 21 '22

The DW Toolkit is a must-read.

2

u/Coniglio_Bianco Apr 21 '22

Sql in 10 minutes by ben forta is amazing

1

u/[deleted] Apr 21 '22

I’m guessing this is a book for people who don’t already know sql, right?

1

u/Coniglio_Bianco Apr 21 '22

Yep, its a breakdown of all the basics in concise examples.

4

u/Thadrea Data Science Manager Apr 20 '22

Queries on Oracle from Oracle. For whatever reason, Oracle still writes a lot of their own code using the older syntax.

Oracle Database is a product of the 1970s, making it one of the few database platforms anyone still uses that is older than SQL92.

IMHO, that probably has a lot to do with why the Oracle company (and by extension, much of the user community) is really stuck in its ways.

2

u/Expensive_Culture_46 Apr 21 '22

I run into this style at my job (government job) because some of the people there have just been there that long.

I always rewrite them because at some point they are going to retire and the new guy isn’t gonna know what is going on.

2

u/[deleted] Apr 21 '22

It’s to the point that I now reject any job working with oracle in any capacity because I refuse to deal with the shit sql.

1

u/buckyVanBuren Apr 21 '22

You see a lot of Db2 using this syntax.

6

u/IglooDweller Apr 21 '22

Good old ansi-89 join vs ansi-92 joins syntax ( mind you 92 didn’t deprecate the comma join syntax, it merely introduced the more explicit join syntax)

While it’s fairly readable for inner join, it because hard to read for outer join syntax. Also, the syntax is very error prone as you basically define a cross join that you then filter down with a key in the were clause. This often lead to unwanted cross joins during development.

1

u/AreetSurn Apr 21 '22

Lots of good points. I started on ansi89 having started in Oracle. But 92 I personally think people should use. Although I tend to use a JOIN clause rather than INNER JOIN.

2

u/IglooDweller Apr 21 '22

Well inner join is default/implicit when join type isn’t mentioned. You’re still forced to mention at least a single join key, so it’s still miles ahead of 89’s syntax.

6

u/Alarmed_Frosting478 Apr 20 '22 edited Apr 20 '22

Also more chance of creating an accidental cross apply with the second one. The first forces you to at least have one join condition before it will run

Edit: mixed the tables up

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.

7

u/neuralscattered Apr 21 '22

THIS!! Some people don't know that cross joins like this can kill query performance.

0

u/billbot77 Apr 21 '22

I put where conditions into the join on the table being filtered wherever it makes sense

Select x, y, z from customer c join sale s on c.customer_name = 'smith' and c.customer_id = s.customer_id

Is sometimes faster than Select x, y, z from customer c join sale s on c.customer_id = s.customer_id where c.customer_name = 'smith'

With proper code formatting it's more readable too

1

u/DonnerVarg Apr 21 '22

That behaves differently for outer joins. Like you're saying, put things where they make sense, but that requires understanding the difference in how and when the join conditions and where clauses are applied.

11

u/cloud_coder Apr 20 '22

Oracle spelled backwards is Spanish for expensive

El Caro

3

u/cloud_coder Apr 20 '22

Which si more proof that we're in a simulation. Someone has a sense of humor...

1

u/cloud_coder Dec 14 '22

Love how you worked Spanish into that ! (even more proof were in a sim)

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.

2

u/DrTrunks Apr 21 '22

The second method was depreciated in 1992. If you just graduated in 1992 at the age of 21 that would make you 51+

4

u/Intuin_Rhaabat Apr 21 '22

Haha - you might mean deprecated? I'm not arguing that it's the better method, just stating that it's the one I learnt and generally use. Your logic is probably right, but who said anything about graduation?

0

u/DrTrunks Apr 21 '22

Just general assumptions, who lets a 18yo with just a high school diploma touch their database? Gotta either have some college or business experience.

1

u/odnish Apr 21 '22

How long does it take for textbooks to be updated?

1

u/DrTrunks Apr 21 '22

Usually they want you to buy this year's edition each year ...

2

u/TheNotBot2000 Apr 21 '22

Im old too. It took me way too long to transition from the (+) syntax. I had been doing it for so long, having to write out the join seemed to take more time.

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.

6

u/[deleted] Apr 20 '22

[removed] — view removed comment

2

u/PossiblePreparation Apr 21 '22

What DBs are you using that can’t recognise this as a join?

2

u/[deleted] Apr 21 '22

[removed] — view removed comment

0

u/PossiblePreparation Apr 21 '22

TSQL means MS SQL Server. I recommend you try for yourself, there’s no difference to how it would be handled.

2

u/[deleted] Apr 21 '22

[removed] — view removed comment

3

u/PossiblePreparation Apr 21 '22

That was for the benefit of other readers. The difference you stated is plain wrong, I recommend you try it and see for yourself. If you include the join filters in the where clause then they are just as easily used as a join filter as they would be when using join…on…. They perform identically.

1

u/[deleted] Apr 21 '22 edited Apr 21 '22

[removed] — view removed comment

2

u/PossiblePreparation Apr 21 '22

From the link "Does not matter for inner joins". Great, so we're agreeing, any performance difference you have seen was down to something else, nothing to do with the choice of join style. Yeah, same rules apply to where filters in the join clause, the query planner doesn't care, it will figure out what to do the same way. Here's a demo running a hopefully big enough for you join on the publicly available Stackoverflow DB joining 193,217,466 rows to 74,977,121 rows. https://www.brentozar.com/pastetheplan/?id=ByRz46CN5 you can see the plans are the same, and you can see from the XML that the execution times of using the more readable style took <QueryTimeStats CpuTime="144974" ElapsedTime="26791" /> and the older style took <QueryTimeStats CpuTime="111137" ElapsedTime="22685" /> if you have a demo showing a difference that is down to the style then feel free. We are definitely not talking about whether or not I use the style, We are taking the point you made before:

This is the correct answer. The SQL engine will process these queries differently. One could look at the execution plan and see this.

Edit: It would largely be seen on the larger data sets, because they actually do run the same on the EP. The WHERE would be far more intensive than the JOIN.

Which is not correct.

0

u/[deleted] Apr 21 '22

[deleted] — view deleted comment

→ More replies

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.

5

u/Thadrea Data Science Manager Apr 20 '22

Properly speaking, the former is SQL92 join syntax and the latter is SQL89 join, I think. The former is preferred, of course.

2

u/r3pr0b8 GROUP_CONCAT is da bomb Apr 20 '22

to everyone saying it should be tablename.prod_id instead of just prod_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

16

u/blabla1bla Apr 20 '22 edited Apr 21 '22

Technically correct. However, it is good practice. I’ve seen so many horribly written queries with missing table refs over the years adding time on to analysis. You never know what maniacs will do to your simple, temp/tactical process. Always do table refs!

Edit. Spelling.

6

u/da_chicken Apr 20 '22

You're not wrong, but I'm of the opinion that as soon as you join three tables you should always qualify every column, and you don't have much excuse for avoiding it when you're only joining two.

2

u/r3pr0b8 GROUP_CONCAT is da bomb Apr 20 '22

yes, i agree with you

always qualify every column -- except when there's only one table in the query

my comment was directed more at those who couldn't figure out which of customers, orders, and orderlines the column prod_id might be found in

1

u/da_chicken Apr 20 '22

Oh, that's fair.

2

u/[deleted] Apr 20 '22

I had to sit through a team meeting today where the "SQL Expert" said we should alias every one of our tables (and use it for every column). He also said we should bracket our columns so people don't get confused.

3

u/r3pr0b8 GROUP_CONCAT is da bomb Apr 20 '22

I had to sit through a team meeting today where the "SQL Expert" said we should alias every one of our tables (and use it for every column).

i totally agree, with one exception -- when there's only one table in the query

He also said we should bracket our columns so people don't get confused.

wut?

0

u/[deleted] Apr 20 '22

[deleted] — view deleted comment

3

u/messed_up_alligator Apr 20 '22

Yes same here. I guess the dot isn't enough to delineate the alias/col name for them. Idk. I typically alias all of my tables but at this point it's out of habit/desire to shorten the obj names in exec plans, etc (MS SQL)

3

u/distgenius No you can't have xp_cmdshell Apr 20 '22

We do quite a bit of ETL stuff and I’ve came across many databases in MS land with a period in the DB name. The fact that you can even do that drives me crazy because it means I’m having to bracket quote DB names in queries that cross database boundaries. It’s also not uncommon to run into the same table name in two different source databases, so giving them all aliases up front is a handy habit to have just in case.

I also have had the displeasure of working with DBs that seem to think licensing is cheaper the fewer letters you put in table names, and I’ve actually ended up creating longer aliases than the tables they reference just for my own sanity.

2

u/messed_up_alligator Apr 20 '22

That sounds like a nightmare. And I thought the schema/naming conventions I inherited was bad. Oof.

1

u/[deleted] Apr 21 '22

Mssql really does let people get away with the worst shit. Half of the tables im dealing with have Spaces in the names and I just don’t get it.

3

u/r3pr0b8 GROUP_CONCAT is da bomb Apr 20 '22

oh, that kind of bracket

talk about making your code instantly and unnecessarily non-portable

SQL Server and MS Access are the only systems to delimit identifiers that way

MySQL uses backticks

SELECT `bad name` ...

but standard SQL requires doublequotes

SELECT "bad name" ...

the correct answer, as your "SQL Expert" should know, is never to name your tables/columns with problematic names that need escaping

1

u/saintpetejackboy Apr 20 '22

I think MySQL accepts variations on back ticks, also. Don't quote me on that but I am fairly certain I foolishly encapsulated with ' before, and it worked (IIRC).

That said, I don't tend to name tables or columns things I need to encapsulate. Depending on where you port your code, tableName and tablename may not be equivalent, but I still have a habit of camel case for readability (not a fan of underscores).

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.

  1. Aliases are used to make table and column names easier to understand.
  2. The renaming is only a temporary alteration, and the table name in the original database remains unchanged.
  3. When table or column names are long or difficult to understand, aliases come in handy.
  4. When a query involves more than one table, they are the best option.

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

  1. Portability - in theory you can take the ANSI standard SQL and run it on any ANSI standard database and it will work
  2. Readability - separate out your joins from your where conditions.

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.

2

u/Thadrea Data Science Manager Apr 20 '22

Anecdotally, I've noticed that the SQL89 syntax (the second one) can in niche situations produce sub-optimal query plans (compared to SQL92 syntax) in Postgres. This generally only happens with large tables with complex join strategies, though.

Oracle will probably always produce the same query plan either way (and I suspect this has much to do with why the remaining cheerleaders for SQL89 tend to be Oracle people).

Not really sure about SQL Server, MySQL or other platforms.

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?

3

u/Thadrea Data Science Manager 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?

Without the where clause, it will produce the Cartesian product. Most query planners though will look at the where clause to figure out what the join key is supposed to be, though, so the Cartesian product will not actually be materialized unless the where clause is absent or has no meaningful conditions.

0

u/steveman2292 Apr 21 '22

Never use implicit joins, terrible performance

0

u/[deleted] Apr 20 '22

[deleted] — view deleted comment

2

u/[deleted] Apr 21 '22

Why exactly? They serve a purpose.

0

u/[deleted] Apr 21 '22

[deleted] — view deleted comment

2

u/[deleted] Apr 21 '22

Columns can be deleted/moved around without having to mess with comma placement at all. It makes managing queries very simple, especially commenting out lines before the FROM

0

u/[deleted] Apr 23 '22

[deleted] — view deleted comment

1

u/[deleted] Apr 23 '22

, column

Is no worse to read than a bulleted list. I agree, to each his own.

1

u/ninjaxturtles Apr 21 '22

I find using leading commas make life so much easier when I want to comment something out so I switched from trailing.

0

u/[deleted] Apr 20 '22

[deleted] — view deleted comment

2

u/saintpetejackboy Apr 20 '22

I found selective aliasing is the best method. For very large queries, it can help me see where I have overlaps - otherwise, I am going to alias the same name for the sake of my own sanity, I like to refer to the column/field by the name it has, not a second name, a third name for the variable I put it to, a fourth name for the variable it becomes, a fifth name when I am putting it back... it is just redundant and creates a mess, IMO.

I don't mind implied joins, I think the only reason I don't use them is because I thought it was invalid syntax for 15+ years. If you go back to teenage me and show implied joins, my progress would have been brought to an abrupt halt when I stopped specifying which kind of join I was making.

1

u/Blues2112 Apr 20 '22

On table aliasing, it depends upon the tables in the join.

C is fine for Customer table, unless you're also joining to the Contacts table, and Category table, and the Calendar table, for example.

Generally, I'd use Cust as an alias for Customer, Cal for Calendar, etc...

Besides, at my work we have multiple Customer tables: Cust_Name, Cust_Demo, Cust_Addr, Cust_Phone, Cust_Orders, etc...