EXPERT RESPONSE
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.
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:
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.
|
