To celebrate our 600th "Ask The Expert" SQL column, we thought we'd run a special three-part answer, to highlight some of the common or frequently asked SQL questions we've seen over the past six years.
More on SQL
Learn guidelines for database index design and optimization
Read about the ways that SQL Server developers hurt performance
Check out this book excerpt about MySQL backup and recovery
Please do keep sending in your questions. We love ’em.
Types of common SQL questions
Common SQL questions fall into three broad categories:
- novice questions
- homework questions
- serious SQL questions
1. Novice questions
Novice questions are usually very simple. They immediately reveal only limited degrees of familiarity with SQL. For example,
- How can I delete all rows before a particular date?
Answer: With a WHERE condition in the DELETE statement. Every basic SQL tutorial covers this.
- I want only rows which have the same IDs in both tables
Answer: Use an INNER JOIN. Actually not a bad question, if you've never seen or heard of joins, which many new programmers apparently have not.
- What is the difference between Distinct and Unique?
Answer: Also quite a reasonable question. They aren't synonyms in SQL, because each can be used only in very specific places in SQL statements—DISTINCT in a SELECT clause or COUNT(DISTINCT) expression, and UNIQUE when declaring a constraint. On the other hand, they are synonyms when talking about SQL, because when you use SELECT DISTINCT, you do get unique rows. So what to answer?
"What is the difference between ..." seems to be a common question.
- What is the difference between "inner" and "outer" joins?
Answer: Outer joins return unmatched rows. What more is there to say?
- What is the difference between GROUP BY and ORDER BY clauses?
Answer: One does grouping, the other does ordering. That might sound flippant, but it is not meant to be.
We really do like the short questions, because it gives us so much latitude in choosing the examples with which to illustrate the topic. But sometimes the question just isn't specific enough.
Then there are the "It depends" questions. These questions often exhibit reasonable knowledge of SQL. For example,
- Which is faster, Functions Or Procedures, and why? Thanks, it's urgent!
Answer: Both functions and procedures require more than a beginner's level of knowledge of SQL. Knowing which is faster might—might—sway the decision to use one or the other. However, too much depends on factors completely outside the question, such as what you're trying to accomplish. Furthermore, it is naïve to think there might be a difference in performance for all contexts. That's about all we can reasonably say on the subject. Try some benchmarks on your own database system. As for it being urgent, this does not influence whether we will answer your question.
Several "it depends" questions seem to come up over and over, and we try to answer them at least once. For example, here's one we've seen many times:
- JOIN, SUB-QUERY, which one is more efficient...
Answer: Which is faster, subquery or join? (4 November, 2005)
We get a steady supply of novice questions, and this is good. It indicates that new blood is constantly coming into the world of relational databases. While most of the questions are not interesting enough to be selected for an answer, we do read them all.
In Common SQL questions, part 2, we'll do a quick review of some common homework questions.
Dig Deeper on Oracle DBA jobs, training and certification
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading