Go to books ↓
It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of them are perfect!
Instead of NOT IN, use a correlated NOT EXISTS for this query pattern. Always. Other methods may rival it in terms of performance, when all other variables are the same, but all of the other methods introduce either performance problems or other challenges.
ORMs can be used to nicely augment working with SQL in a program, but they should not replace it
Finding the min, max, and avg price of an order in your purchases table is easy. But what about the median price? Medians are much more difficult for the database to compute, so there usually isn't a built-in function for this.
An outer join is used to match rows from two tables. Even if there is no match rows are included. Rows from one of the tables are always included, for the other, when there are no matches, NULL values are included.
SQL has gone out of fashion lately—partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011.
This site helps you exploring modern SQL. Features are explained in simple terms. Use-cases demonstrate their power. Compatibility is well documented.
COUNT(*) needs to return the exact number of rows. EXISTS only needs to answer a question like: “Are there any rows at all?”