UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> SELECT    
SELECT

SQL expects the elements, or clauses, of a statement to be in a certain order. This is a complete listing of all possible clauses in ACE SQL. Note that all clauses except SELECT are optional. For most practical purposes, though, FROM is required as it's rare that one would do a SELECT query that doesn't select anything from tables.

[ PARAMETERS <column definition>[, ... ] ]
[ TRANSFORM <aggregate expression>[, ... ] ]
SELECT <column expression>[, ... ]
[ FROM <table expression> ]
[ { [ INNER ] | LEFT | RIGHT } JOIN <table expression> ON <where expression>[, ... ] ]
[ WHERE <where expression>[, ... ] ]
[ GROUP BY <grouping expression>[, ... ] ]
[ HAVING <having expression>[, ... ] ]
[ ORDER BY <column expression>[, ... ] ]
[ PIVOT <column expression>[, ... ] ]


Contents

SELECT

Lists all columns, expressions, or subqueries that will be included in a query. One may use an asterisk (*) to select all columns from a table.

A simple select of columns from a table:

SQL
SELECT MyFirstColumn, MyOtherColumn, MyAnotherColumn...

Same but with two expressions instead of columns from a table:

SQL
SELECT MyFirstColumn, Date(), CurrentUser() ...

Selecting a constant:

SQL
SELECT 1...
SELECT "Hello, World!"...

Selecting from a subquery. Note that the subquery must return only one column, one row for a SELECT clause!

SQL
SELECT (SELECT Max(MyColumn) FROM aTable)...

Columns in SELECT may be aliased to provide more meaningful names:

SQL
SELECT Date() AS "Today's Date", 7 AS "Your Lucky Number"...

Note that quotes are required if the alias contains non-alphanumeric characters. Typically, this is not necessary for presenting the results of a query, as that can be left to label controls on forms/report, but aliases may be quite useful when referencing a column in another query. For that reason, developers typically use the same naming rules they use with any other objects such as:

SQL
SELECT Date() As CurrentDate, 7 AS LuckyNumber...


FROM

Lists all sources for all columns listed in the SELECT clause. The valid objects that may be listed in a FROM clause are table, query, or sub-query.

Selecting from a single table:

SQL
SELECT a, b, c
FROM myTable;

Selecting from a query saved in Access:

SQL
SELECT a, b, c
FROM mySavedQuery;

Selecting from a subquery. Note that there is no restriction on the number of columns or rows returned by subqueries in a FROM clause:

SQL
SELECT a, b, c
FROM (
SELECT a, b, c
FROM aTable
) AS aVirtualTable;

Note that an alias is required for subqueries in a FROM clause.

JOIN

To relate several different tables together in a single query, it may be desirable to join the tables together.

INNER JOIN

Default joining behavior; return only rows where matches occurs from both tables.

OUTER JOIN

Returns both matching and non-matching rows. Which non-matching rows will be returned is determined by the direction of joins, specified by either LEFT or RIGHT keywords and which tables appear. Thus this syntax will produce identical output:

SQL
Table_A LEFT JOIN Table_B
SQL
Table_B RIGHT JOIN Table_A

As a matter of convention, it is preferable to stick to LEFT JOIN to keep queries consistent, especially in how it is read. Note this will not produce the same output as the two previous examples:

SQL
Table_B LEFT JOIN Table_A

The difference between those two LEFT JOINs works this way:

Table_A LEFT JOIN Table_B: Return all rows from Table_A and any matching rows from Table_B. For rows from Table_A that do not have a matching row from Table_B, assign a value of null to all Table_B's columns for this row.

Table_B LEFT JOIN Table_A: Return all rows from Table_B and any matching rows from Table_A. For rows from Table_B that do not have a matching row from Table_A, assign a value of null to all Table_A's columns for this row.

ON

Defines how a match should be determine, operating similarly to the WHERE clause, expecting a expression that evaluates to true/false. In most cases, it is equi-join:

SQL
ON Table_A.ID = Table_B.ID

but equality is not the only operator allowed. It is possible to do this:

SQL
ON Table_A.ID > Table_B.ID

The difference is that in first ON criteria, only rows that shares a identical value (e.g. ID) from both table will be included in INNER JOIN. In second criteria, a single row from Table_A will be associated with as many Table_B rows as where Table_B rows' ID is less than Table_A's ID.

Complete JOIN samples

Every JOIN keyword must have a corresponding ON clause. Here's a simple JOIN:

SQL
SELECT *
FROM tblPerson
INNER JOIN tblFamily
ON tblPerson.FamilyID = tblFamily.FamilyID;

Here, the query will show a row representing all information on this one person, as well the family the person is member of. Of course, there may be people for whom we do not have any family information. In such cases, those would be excluded from the above query. To ensure we see everyone, regardless whether we know anything about their family or not:

SQL
SELECT *
FROM tblPerson
LEFT JOIN tblFamily
ON tblPerson.FamilyID = tblFamily.FamilyID;

But what if we want to see families that have no people related so we can clean up the records? One of a few different ways is to again use the outer join but with a criteria:

SQL
SELECT *
FROM tblFamily
LEFT JOIN tblPerson
ON tblFamily.FamilyID = tblPerson.FamilyID
WHERE tblPerson.FamilyID IS NULL;

Note the additional criteria, "WHERE Person.FamilyID IS NULL". When we join two tables with an outer join, any non-matches will have nulls as a placeholder to indicate that there are no related record from other table for this one record we're looking at. Thus, with the Is Null criteria we only see non-matches, that is, families with no people associated to this family. This type of join is widely known as a "Frustrated Join".

In some cases, we need to join a table to itself. A common example is when we have an Employees table. Some employees in that table are actually supervisors. Suppose we want to run a query to show who is a given employee's immediate supervisor. We would do that with a self-join. However, a self-join requires that we provide an alias:

SQL
SELECT emp.*, sup.empName
FROM tblEmployees emp
LEFT JOIN tblEmployees sup
ON emp.supervisorID = sup.employeeID;

Note that the table 'employees' appears twice but is aliased as emp, for the employee records, and sup, for the supervisor records. Furthermore, note that the ON clause references different columns from the respective table, enabling us to match a different employee as a supervisor to this employee.

All examples so far have shown the usage of the equi-join, which easily constitutes the large majority of queries written out there. However, that does not mean there aren't any uses for non-equi-joins. A common problem people face is when they want to see a whole row representing the maximum, minimum or likewise value. Suppose we want to see what are the most expensive cars each dealer sells. One way to do this is to use non-equi-join:

SQL
SELECT dealer, make, model, caryear, price
FROM tblCars AS c1
LEFT JOIN tblCars AS c2
ON c1.dealer = c2.dealer AND c1.price < c2.price
WHERE c2.price IS NULL;

Similarly to the frustrated join, we make use of the fact that non-match will contain a null value, and when we're on a row where the price is at its maximum, there will be no match so we know that this is in fact the maximum price for a given dealer.

More than 2 tables in a join

All of our examples so far show only two tables participating in a join. However, it is legal to have an arbitrary number of tables joined together. An example is when we want to list customer and order information to be associated with a certain line order:

SQL
SELECT c.FullName, o.OrderDesc, o.OrderDate, d.*
FROM (
tblCustomers AS c JOIN tblOrders AS o
ON c.CustomerID = o.CustomerID
) JOIN tblOrderDetails AS d
ON o.OrderID = d.OrderID;

Though the SQL standard does not require the (), ACE SQL does in fact require () when associating more than two tables in a join. As the number of joins increases, the ()s could get quite numerous. Suppose we wanted to show a lookup table's value and thus need to add the lookup table:

SQL
SELECT c.FullName, t.CustomerType, o.OrderDesc, o.OrderDate, d.*
FROM (
(
tblCustomers AS c JOIN tlkpCustomerTypes AS t
ON c.CustomerTypeID = t.CustomerTypeID
) JOIN tblOrders AS o
ON c.CustomerID = o.CustomerID
) JOIN tblOrderDetails d
ON o.OrderID = d.OrderID;

Tracking what ()s are required to join several tables may get confusing. and for that reason, it may be simpler to use the graphical builder to select the tables and join them graphically. You can then look at the SQL view to see what it actually looks like. Alternatively a useful way to view the () is to pretend when you resolve a JOIN in a (), it becomes a new table that contains both rows from each table and you're back to doing just another 2-way join. In this sense, this can be said to be equivalent:

SQL
... FROM (
tblCustomers AS c JOIN tlkpCustomerTypes AS t
ON c.CustomerTypeID = t.CustomerTypes
) JOIN ...
SQL
... FROM qryCustomerExtended
JOIN ...

Where the qryCustomerExtended contains this FROM clause:

SQL
FROM tblCustomers AS c
JOIN tlkpCustomerTypes AS t
ON c.CustomerTypeID = t.CustomerTypeID


WHERE

Defines which rows may be returned by this query. Accepts any expressions that will evaluate to True/False.

Get only rows where myColumn has a value of 1.

SQL
... WHERE myColumn = 1;

Get only rows where myColumn is in a certain range:

SQL
... WHERE myColumn BETWEEN 0 AND 100;

Get a wildcard match for any strings starting with "My":

SQL
... WHERE myColumn LIKE "My*"

Compare a value against a subquery. Like SELECT, the subquery must return only one column & one row.

SQL
... WHERE myColumn = (SELECT MIN(myColumn) FROM otherTable);

EXISTS

There is also a special operator that does not directly reference any column but rather returns a boolean result directly. To show only rows where a certain condition is true in another query, we can use EXISTS:

SQL
... WHERE EXISTS (SELECT Null FROM otherTable WHERE ID = 2)

First thing to note is that what we actually SELECT in a subquery is fully irrelevant. It does not matter what we actually SELECT; whether it's Null, 1, 0, "abc", some column - it's simply not checked. The only thing that EXISTS operator will look at is the WHERE clause of the subquery.

This is really simple and not very useful because the query will then either return all rows or none depending on whether there is a ID containing value of 2 in the otherTable. Typically, we use EXISTS in a correlated fashion, referencing the outer query. Here's a fuller example:

SQL
SELECT *
FROM tblWidgets AS w
WHERE EXISTS (SELECT Null FROM tblOrderDetails AS od WHERE od.WidgetID = w.WidgetID);

We get to select all rows from the Widget tables for where there were never any widgets ordered by anyone ever. That would be handy in helping us clearing the inventory of poor-performing widgets. Note that we can do exactly same thing with a subquery:

SQL
SELECT *
FROM tblWidgets AS w
WHERE w.WidgetID IN (SELECT od.WidgetID FROM tblOrderDetails AS od);

Note that with a IN() operator, we must select the right column. However, the difference between a EXISTS operator and IN() operator is that EXISTS does not actually return the result of a subquery, but just a boolean result. Thus, the IN() operator requires two evaluations; one to execute the subquery, then determine whether the outer query's column is found within that subquery. EXISTS does that in a single step. So in general, EXISTS operator will be more effective than a equivalent IN() operator.


GROUP BY

Used in aggregating, this defines how rows should be grouped. If we were to sum all quantities of ordered widgets like thus:

SQL
SELECT SUM(Quantity)
FROM tblOrderDetails;

That will tell us the grand total of quantity we have sold since the very first day we start taking orders. But that won't tell us the quantity of specific kinds of widgets we may have sold. For that, we add a GROUP BY:

SQL
SELECT WidgetID, SUM(Quantity)
FROM tblOrderDetails
GROUP BY WidgetID;

Now we can see the totals of different kinds of widgets we've sold ever since we opened our doors. But maybe we want to see who our best customers are in terms of dollar amounts:

SQL
SELECT CustomerID, SUM(Price)
FROM tblOrderDetails
GROUP BY CustomerID;

Now we can see who are our favorite customers and stingiest customers but that won't tell us what kind of widgets they might have bought with their money. Thus we could further sub-group:

SQL
SELECT CustomerID, WidgetID, SUM(Price)
FROM tblOrderDetails
GROUP BY CustomerID, WidgetID;

Now we can see what widgets a customer has bought and how much they have paid in total for the given widget.


HAVING

Similar to WHERE but occurs after the aggregating. Do not use HAVING where the evaluations can be applied without the aggregating.

A good example for HAVING is returning only groups where a count (e.g. an aggregation) is greater than a criteria:

Return only groups with count greater than 2:

SQL
... HAVING Count(MyColumn)>2

Return only groups with a minimum value larger than 100:

SQL
... HAVING Min(MyColumn)>100;



ORDER BY

Determines how the rows returned by the query should be sorted. Accepts either columns or expressions.

Return in ascending order:

SQL
... ORDER BY MyColumn;
... ORDER BY MyColumn ASC;

Return in descending order:

SQL
... ORDER BY MyColumn DESC;


TRANSFORM and PIVOT

TRANSFORM and PIVOT work together to enable data to be transposed from rows into columns. This is called a CrossTab query.

The fields (/expressions) to be grouped and aggregated are specified by TRANSFORM.

Column headings will be specified by PIVOT.

General Syntax

example:

SQL
TRANSFORM Count(B.Fieldname1)
SELECT
A.Fieldname2
, A.Fieldname3
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2
, A.Fieldname3
PIVOT B.Fieldname1;

The PIVOT keyword can also be stated as PIVOT BY

NOTE: this example assumes that names do not contain spaces or special characters.

Pivot Equations

You can also use equations to pivot. For instance, if you want the column headings to be year and month, you can do this:

SQL
PIVOT Format([DateField],'yyyy-mm')


Specify Order of Columns

if you want month names in chronological, instead of alphabetical, order, you can specify the exact order using IN (List):

SQL
PIVOT Format([DateField],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")


Show a Column with Totals

If you also want a column that totals whatever you have in all the VALUE columns (the expression after TRANSFORM), repeat your transform expression in the SELECT and GROUP BY phrases. For instance, notice that Count(B.Fieldname1) is now in 2 more places of the SQL statement:

SQL
TRANSFORM Count(B.Fieldname1)
SELECT
A.Fieldname2
, A.Fieldname
, Count(B.Fieldname1) AS YourColumnName
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2
, A.Fieldname3
, Count(B.Fieldname1)
PIVOT B.Fieldname1;


Practical Example

For a practical example, assume you have a Sales table and an Items table with these fields:

Sales
  • DateSale, date
  • ItemID, long integer -- FK (foreign key) to Items
  • Qty, long integer
Items
  • ItemID, autonumber -- PK (primary key)
  • Item, text

To see the total number sold of each item for each month, here is an example SQL statement:

SQL
TRANSFORM Sum(Qty) AS SumQty
SELECT
Item
, Sum(Qty) AS TotalQty
FROM Sales
INNER JOIN Items
ON Sales.ItemID = Items.ItemID
GROUP BY
Item
, Sum(Qty)
PIVOT By Format(DateSale,"yyyy-mm");


Here are some example RESULTS:

Item   TotalQty   2010-01   2010-02  2010-03
Bells       15       5         6        4
Whistles     9       2         7
Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 17,283 times.  This page was last modified 01:43, 10 February 2012 by Jack Leach. Contributions by George Hepworth, pacala_ba, BananaRepublic and strive4peace2010 and others  Disclaimers