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?”
A social scientist in Italy, Vilfredo Pareto discovered that roughly 20% of the population owned 80% of the wealth. From there, he noticed that these proportions described many other aspects of society. This led him to propose the 80-20 rule. The Pareto chart, named in his honor, shows the large contribution from a small proportion of the population.
After years of being left for dead, SQL today is making a comeback. How come? And what effect will this have on the data community?
If you are a software engineer and you have just enough SQL to write queries that count, sum, average join and maybe sub-select, then I’m writing this post for you. If, when you need more complicated analysis or computation, you pull your query results into excel or your favorite scripting language to do more processing, then I have some good news.
Some applications, such as todo lists, need to maintain a user-defined order of items. The challenge is that the order is arbitrary and can change when the user rearranges items.
In this SQL window functions tutorial, we will describe how these functions work in general, what is behind their syntax, and show how to answer these questions with pure SQL.
These technologies have their place, if anything, Amazon has proven their effectiveness. But if you’re running a small online store with between 1,000 - 10,000 customers, then you can still very much live on SQL. Besides, the ML/AI talents aren’t a dime a dozen.
The SQL language is quirky. This is mostly because the syntactical order of operations doesn’t match the logical order of operations. The syntax is meant to be human readable (remember Structured English Query Language?) but when reasoning about a SQL statement, we would often like to directly write down the logical order of operations.