SQL FAQ: Common SQL questions, part 1

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle tutorials, tips and FAQs SearchOracle.com podcasts Do I need to buy an Oracle license for a development database? Special Report: Collaborate '09 IOUG on 2009 Collaborate conference: what's hot, what's not How to create a database link in Oracle Most clicked stories of 2007 How do I do that in Oracle? Counting a row's NULL columns Data integration challenges and solutions revealed How many redo log files? Oracle tutorials, tips and FAQs Research Oracle and SQL Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to use the Oracle Database SQL Reference Manual How to use SQL Developer to run SQL statements How to work with the Oracle database home page How to use SQL*Plus in Oracle How to use SQL Developer to work with an Oracle database How to view and edit table column definitions How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join Oracle and SQL Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary 10g  (SearchOracle.com) delimiter  (SearchOracle.com) extent  (SearchOracle.com) field  (SearchOracle.com) flexfield  (SearchOracle.com) foreign key  (SearchOracle.com) Oracle  (SearchOracle.com) row  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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:

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:

• 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.