SQL FAQ: Common SQL questions, part 1

This three-part answer addresses some of the most frequently asked questions about SQL. Part one covers novice questions.

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:

  1. novice questions
  2. homework questions
  3. 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.

Dig Deeper on Oracle DBA jobs, training and certification

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.