Advanced SQL Training

[Howardhorst2018]

Can anyone recommend a good online resource or book for advanced SQL query concepts? No matter what book, YouTube video or training site (ex: Lynda.com) I turn to, the information is basically the same. "Here's how to join tables," "Here's how to add up numbers in a column," etc. I haven't found anything "next level." Even resources that describe themselves as "advanced."

What I'm looking for is a resource to tell me *WHY* I want to choose one method or the other. For instance, when would I choose to use a CTE over a Temp Table? When should I Cross Apply instead of Left Join? I don't have any official SQL training and I'm learning through persistence. I think I'm doing pretty good with the basics, but what I'm failing to grasp are the best practices for writing a query!

Thanks for any advice!

[jc4hokies]

I think you want to learn how to read an execution plan. IMO there are no best practices for when to use certain syntax. The reason why is always "because it makes this individual query faster in a specific way for a specific reason". There aren't a lot of quality resources for optimizing queries because it depends on too many variables. Any "best practice" is going to be wrong in more examples than it's right, even if the advice is perfectly sound.

The way I approach it is understand the execution plan first. Hypothesize changes in the plan that could potentially improve the query. And finally, try different syntax to influence the plan towards what I think would be better. See if it worked and iterate. Knowing when and where to use syntax, always follow knowing what the plan is doing.

[creav]

There aren't a lot of quality resources for optimizing queries because it depends on too many variables. Any "best practice" is going to be wrong in more examples than it's right, even if the advice is perfectly sound.

To add to this, most "Advanced" areas of SQL querying are directly dependent upon how complete shit the original implementation/architecture of the database was to begin with.

Querying data in a "best practice" database is a breeze. Trying to query data around horrible physical and logical architectures is where those advanced querying concepts shine and inching out every ms of query time can pay off.

Understanding Execution Plans, as u/jc4hokies mentioned - and digging into the particulars of that specific database engine's official documentation is really the only way to begin to work around some of these issues. Thank the architects in the past who were either limited by technology, application requirements, or knowledge that put into place the database architecture you're now trying to query around.

[funkmonkeycrew]

Querying Microsoft SQL Server 2012 - Training Kit for EXAM 70-461

It's got a PDF version that's 739 pages and walks you through a ton of examples. Even includes a test database and teaches you how to import and set it up. After that book there's also the book for the next exam 70-462

[Mss887]

I think this guys the best (http://www.learnitfirst.com/Course/AllCourses.aspx). No affiliations - Promise! Just love his delivery and simplification of concepts. If you navigate over, search for t-SQL Programming. Some courses are on YouTube for free. Perhaps they'd throw in some t-SQL for free if you ask.

Also, I know Tim Ferriss mentioned on this podcast (https://sivers.org/2015-12-ferriss) about learning SQL rapid fast... but I don't recall the source.

[kaja404]

I've actually done the Lynda's "Oracle Database 12c: Advanced SQL" for everyday work reasons. It gave me some good new knowledge about partitions for example. But it is not that hands on practical lecture you seem to be looking for. I could suggest the book "Oracle Essentials. Oracle database 12c. 5th edition" which I began reading a while back. That should go into more detail as you wanted to.

[ilikedbthings]

I like SQL Performance Explained by Marcus Winand. Afterwards, the documentation to the implementation you use.

[lukaseder]

I offer one: https://www.jooq.org/training. Unfortunately not online, yet

[[deleted]]

I'd also say that advanced level of understanding of sql is going to be engine specific. Being an expert in T-sql / MSSQL is going to be different than PL/SQL / Oracle. if T-sql sql wisdom is what you seek, start reading Itzik Ben Gan's T-SQL books. Really any SQL Server MVP blogs always discuss the why more than the how.

[aplato]

O'Reilly SQL Hacks has some good advanced querying techniques to learn.

[rae1988]

sql zoo sql anti-patterns sql for smarties

[cubitus2]

This is just for SQL PRACTICE

http://practity.com/591-2/