Hack 62. Issue Queries Without Using a Table

Some queries don't actually need a table in order to work, but if you don't need a table what do you put in the FROM clause? Each of the popular platforms has its own approach to providing a dummy one-row table. You cannot use these tables to store values, but they can still be useful for accessing system functions or performing calculations.

In MySQL, PostgreSQL, and SQL Server, you can drop the FROM clause of the SELECT statement. You will get a single row with no columns, other than the constants that you might specify on the SELECT line. This Zen-like structure can be handy for viewing database constants, exploring built-in functions, or performing calculations.

Oracle won't let you skip the FROM clause, but it does give you a single-row table called dual that has one row and one column.

MySQL also has the dual table, even though you don't usually need it. You do need to use FROM dual in MySQL if you have a WHERE clause.

Suppose that you have fallen asleep at your desk. You wake up confused, not knowing who you are or what the date is. Fortunately, you have an SQL command line in front of you, so you can recover both of these in an instant.

For MySQL you enter:

mysql> SELECT CURRENT_USER, CURRENT_DATE;

You'll get these results:

+-----------------+--------------+
| CURRENT_USER | CURRENT_DATE |
+-----------------+--------------+
| scott@localhost | 2006-06-26 |
+-----------------+--------------+
1 row in set (0.00 sec)

For SQL Server you enter:

1> SELECT SYSTEM_USER, GETDATE( )
2> GO

At the Oracle prompt you enter:

SQL> SELECT USER, CURRENT_DATE FROM dual;

There is an additional bonus in Oracle; by asking for CURRENT_TIMESTAMP you get the time and the time zone. The +01:00 in the following code means GMT plus one, which narrows down your location to the UK, Ireland, or Portugal:

SQL> SELECT USER, CURRENT_TIMESTAMP FROM dual;

USER
----------------------------------------------------------------------------
CURRENT_TIMESTAMP
-----------------------------------------------------------------------
SCOTT
26-JUN-06 10.45.27.045692 +01:00

For Access, you must create a new database (select Fileimages/U2192.jpg border=0>Newimages/U2192.jpg border=0>Blank Database), add a query (select Insertimages/U2192.jpg border=0>Queryimages/U2192.jpg border=0>Design Mode), switch to SQL View (right-click on the query and select SQL View), and type in the following query:

SELECT ENVIRON('username'), Date( )

Select Queryimages/U2192.jpg border=0>Run to execute this query.

8.5.1. Some Useful Static Functions

Certain static functions return the current username, current date, and current timestamp. You can also determine the version number of the database system. A random number may be useful in some cases. A globally unique identifier (GUID, UUID, or NEWID) is a random string that is certain to be unique.

8.5.1.1. MySQL

Here is how you'd do this under MySQL:

SELECT CURRENT_USER,
 CURRENT_DATE,
 CURRENT_TIMESTAMP,
 VERSION( ),
 RAND( ),
 UUID( )

 

8.5.1.2. SQL Server

This is the code you'd use for SQL Server:

SELECT SYSTEM_USER,
 CAST(CAST(GetDate( ) AS INT) AS DATETIME),
 GetDate( ),
 @@VERSION,
 RAND( ),
 NEWID( )

 

8.5.1.3. Oracle

For Oracle, you'd do the following:

SELECT USER,
 CURRENT_DATE,
 CURRENT_TIMESTAMP,
 (SELECT banner FROM v$version WHERE ROWNUM=1),
 DBMS_RANDOM.RANDOM( ),
 SYS_GUID( )
FROM dual;

 

8.5.1.4. PostgreSQL

Under PostgreSQL, use this code:

SELECT CURRENT_USER,
 CURRENT_DATE,
 CURRENT_TIMESTAMP,
 VERSION( ),
 RANDOM( )