Database Tutorial

Course Tutorial Site

Site Admin

Single Table Query

with 16 comments

This explains how a simple query works against a single table with and without aggregation. Probably the three significant pieces of information presented are: (a) the order of execution for the clauses of a SELECT statement; how to work with null values as column returns values and as comparison values in the WHERE clause; and (c) how to compare various data types in Oracle, MySQL, and SQL Server.

The SELECT clause isn’t actually read first. The FROM clause is read first. When that clause is read, any table aliases assigned in the FROM clause are substituted for the full table name. This means when a query has two columns that use the same name, you can type the short alias instead of the full table name. That’s because the alias replaces the table name for the balance of the statement parsing.

Column references don’t require prepending a column with the table or view alias when they’re unique in the result set. Using the aliases does add clarity in any query. You do need to prepend table aliases or the full table names when two or more columns returned by the query have the same column names. You typically encounter this situation when you’re joining two or more tables on primary and foreign key columns that use the same column name(s). Failure to identify these duplicated column names by using the table or alias name causes the SQL parser to trigger an exception. Oracle’s error states that they’re ambiguously defined in the result set.


While the column aliases use the optional AS keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH statement (ANSI SQL: 1999) – blogged on the WITH statement here.

Queries are viewed in two phases. One is selection and the other is projection.

Selection comes in two phases, identifying the sources or tables in the FROM clause, and then filtering the sources based on comparisons in the WHERE clause. The WHERE clause filters the result set and determines how many rows are returned for a query or other DML statement. It is essential for you to understand how to perform comparisons in the WHERE clause, manage null values in those comparisons, and handle null values returned in the SELECT clause.

After learning how to filter single tables, you need to learn how to join tables into combined result sets. The filters in the WHERE clause may be applied against columns from either table in a join.

Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM clause as part of a ON or USING subclause when queries use key words like JOIN et cetera. You find join statements in the WHERE clause when the queries list tables as comma separated elements in a FROM clause. This is a complete page on SQL joins.

Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT clause.

While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.


Aggregation typically happens after selection but before projection. While columns in the SELECT clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY clause.

Sorting by a column is done through the ORDER BY clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY clause.

Query ITEM Table






FROM item i

Written by michaelmclaughlin

October 8th, 2009 at 8:42 pm

Posted in

16 Responses to 'Single Table Query'

Subscribe to comments with RSS or TrackBack to 'Single Table Query'.

  1. In the MySQL example for IFNULL the paragraph says that this is Oracles implementation.

    Peter Sumsion

    1 Feb 10 at 3:55 pm

  2. Great catch. I’m proof reading the copy again tonight. This part is actually moving to the other page. Right now it’s in both places.


    1 Feb 10 at 4:09 pm

  3. Hey, I was just reading about the =ALL and in the 3rd paragraph in that section it was accidentally called =AND.

    Rachelle Haynes

    3 Apr 10 at 10:20 pm

  4. Thanks, it’s fixed now.


    5 Apr 10 at 7:57 pm

  5. At the top, should this:

    “Column references don’t require prepending a column with the table or view alias when they’re not unique in the result set.”

    be this:

    “Column references don’t require prepending a column with the table or view alias when they’re unique in the result set.” (removed the not)

    It sounds like the original is saying that you don’t need to prepend columns with a table name even if they are the same. Or is this statement saying:

    “Even if columns have the same results, you don’t have to prepend each column with the table or view alias as long as the column names in each table are unique”

    Chris Staber

    15 Oct 10 at 9:54 am

  6. Is there any differences between Lower() and Upper() functions?

    Atsuko Takanabe

    16 Oct 10 at 4:49 pm

  7. One promotes to the ASCII uppercase and the other demotes to ASCII lowercase. Other than that, not really.


    18 Dec 10 at 11:51 pm

  8. There was a typographical error addressed in a comment above, regarding =AND being published instead of =ALL. On the same line, conditions is spelled “conditoins.”

    Then in the fifth paragraph the same =AND also exists along with another “conditoins.”


    2 Feb 11 at 10:12 am

  9. The =ALL operator doesn’t make sense to me, where does it have a useful application? Also the =ALL has the same definition as the definitions of IN, =ANY, =SOME.

    Joshua Floyd

    28 Feb 11 at 5:13 pm

  10. In the NULL SQL Functions under the Oracle section it makes mention of data types that implicitly convert. Do you have any examples of these? Thanks in advance.


    18 May 11 at 10:28 am

  11. When I took this class, I was so confused when I encountered =ALL. It just seemed so useless.
    But then I recently discovered the ANY, SOME, and ALL keywords don’t have to be used with “=”. You could say >ALL or <ALL. Suddenly that keyword became useful.
    If your students now think similarly to me, they'd probably be interested to know that.

    Brandon MacGregor

    2 Apr 13 at 9:26 am

  12. Brandon, Thanks for the note!


    18 Mar 14 at 11:23 am

  13. conditoins = conditions


    18 Oct 14 at 4:24 pm

  14. Joshua, I haven’t found a use for the =ALL operator too often but it’s role occurs while trying to find duplicate data sets. These typically occur because of a coding error.


    22 Oct 14 at 1:59 am

  15. You can check the Oracle document, like Datatype Comparison Rules.


    22 Oct 14 at 2:29 am

  16. Chris, Thanks! It’s fixed.


    25 Oct 14 at 6:46 pm

Leave a Reply