Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Aug 27, 2010

MySQL SELECT Statement 101

By Rob Gravelle

The SELECT statement is without question the most complex amongst MySQL's data manipulation statements. This article covers the syntax of the SELECT statement, exploring its many optional clauses.

The SELECT statement is without question the most complex amongst MySQL’s data manipulation statements. Not surprising when you consider that the foremost purpose of structured query language (SQL) is to retrieve information from a relational database which adheres to a given criteria. Entire books have been written about how to construct a query to fetch the data that you’re after. That’s not the purpose of this article. Our goal will be to cover the syntax of the SELECT statement and gain an understanding of its many optional clauses. We’ll start at the beginning and work our way through them. When we’re done, you’ll understand which clauses to use for different purposes. We’ll leave the twenty table joins for another day.

Here’s some code to create and populate a couple of tables. If you’ve been following my data manipulation statements series thus far, you’ll recognize them as the same ones that we’ve used previously.

#
# Table structure for table 'employees'
#
CREATE TABLE /*!32312 IF NOT EXISTS*/ "employees" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "shop_id" int(11) NOT NULL DEFAULT '0',
  "gender" enum('m','f') NOT NULL,
  "name" varchar(32) NOT NULL,
  "salary" int(11) DEFAULT NULL,
  PRIMARY KEY ("id","shop_id"),
  KEY "Foreign Key" ("shop_id")
) AUTO_INCREMENT=15;
#
# Data for table 'employees'
#
LOCK TABLES "employees" WRITE;
/*!40000 ALTER TABLE "employees" DISABLE KEYS;*/
INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
    VALUES (1,1,'m','Jon Simpson',4500);
INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
    VALUES (2,1,'f','Barbara Breitenmoser',NULL);
INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
    VALUES (3,2,'f','Kirsten Ruegg',5600);
INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
    VALUES (4,3,'m','Ralph Teller',5100);
INSERT INTO "employees" ("id", "shop_id", "gender", "name", "salary") 
    VALUES (5,3,'m','Peter Jonson',5200);
/*!40000 ALTER TABLE "employees" ENABLE KEYS;*/
UNLOCK TABLES;
#
# Table structure for table 'shops'
#
CREATE TABLE /*!32312 IF NOT EXISTS*/ "shops" (
  "shop_id" int(11) NOT NULL AUTO_INCREMENT,
  "shop" varchar(32) DEFAULT NULL,
  PRIMARY KEY ("shop_id")
) AUTO_INCREMENT=4;

#
# Data for table 'shops'
#
LOCK TABLES "shops" WRITE;
/*!40000 ALTER TABLE "shops" DISABLE KEYS;*/
INSERT INTO "shops" ("shop_id", "shop") 
    VALUES (1,'Zurich');
INSERT INTO "shops" ("shop_id", "shop") 
    VALUES (2,'New York');
INSERT INTO "shops" ("shop_id", "shop") 
    VALUES (3,'London');
/*!40000 ALTER TABLE "shops" ENABLE KEYS;*/
UNLOCK TABLES;

The Easiest Query You Can Write

Syntax #1:

SELECT expression;

As in:

SELECT 'Easy!';
Outputs
Easy!

Were you expecting more? At its core, the SELECT outputs whatever comes after it, much like the old C/C++ printf function. Although it’s usually used to extract data from one or more tables, it also accepts expressions. It just so happens that literal values are a type of expression. Similarly, numeric values and null are equally valid.

SELECT 4;
Outputs
4
SELECT null;
Outputs
(null)

Another type of expression is the output of one or more functions. By applying both built-in and custom stored functions to a value, you can manipulate the data to suit your desired specifications. The following combines the CURDATE() and DATE_FORMAT() functions to output the weekday, month name, and year of the current date:

SELECT null;
Outputs
(null)

Outputs something like:

DATE_FORMAT(CURDATE(), '%W %M %Y')

Tuesday August 2010

Even the MySQL documentation defines the expression (select_expr) as indicating “a column that you want to retrieve”. As we’ll see later on, there are times that you want to specify a value that is not in a table column.

Column Aliasing

As our first example illustrates, expressions are returned with the expression code in the column header. This helps to identify what produced the returned values. Sometimes, you may want something shorter instead. You can rename any column to a value of your choosing using column aliasing. Just append the “AS 'column name'” after the expression:

Syntax #1b:

SELECT expression AS 'column name';

Note that the quotes around the column alias are not required for single words, but it is a good practice to always use them.

Here is the same query as above with an alias:

SELECT DATE_FORMAT(CURDATE(), '%W %M %Y') AS 'Today\'s Date';

Outputs:

Today's Date

Tuesday August 2010

The presence of the apostrophe in the name complicates matters ever-so-slightly because it uses the same ASCII value as the single quote. There are two ways to handle this. One is to escape it by including a backslash (\) before it, as I did here. The other is to use double quotes around the alias name instead of single ones; both single and double quotes are valid string delimiters.

The Standard Query

Our second form of the SELECT statement is probably more familiar to you. It retrieves data from one or more table columns. The optional WHERE clause can be added to filter the results based on a given criteria set.

Syntax #2:

SELECT expression [, select_expr ...]
FROM table_reference [, table_reference ...]
[WHERE where_condition];

Without the WHERE condition, the query will return every row in the table. The usefulness of such a query is to reorder table columns and/or remove some non-pertinent ones from the resultset. Here’s a query that omits the ID fields as well as reordering the name and gender columns. There are also a couple of function calls to format the salary as currency.

SELECT name, gender, CONCAT('$', FORMAT(salary, 2)) AS 'Salary' FROM employees; 

Here is the resulting record set:

name

gender

Salary

Jon Simpson

m

$4,500.00

Barbara Breitenmoser

f

(NULL)

Kirsten Ruegg

f

$5,600.00

Ralph Teller

m

$5,100.00

Peter Jonson

m

$5,200.00

Querying from Multiple Tables

The real power of the SELECT statement becomes apparent when you combine tables in a meaningful way. When data is fully normalized, it can be difficult to see how tables relate to each other. That’s where table joins come in. By linking tables by related columns, we can gain a clearer picture of their relationship. Say we wanted to know which employees worked in the Zurich shop. The way to link the tables would be via the common shop_id field. Additional filtering criteria come after the AND. The reason that I say additional is because joining tables in this way only returns rows where both tables contain the same values. If either table contains a shop_id that does not link to the other table, these orphaned rows do not appear in the result set. There are other types of joins that can be used to get around this. There is an excellent article on join types on the developer.com website. The MySQL Docs also contain useful information about table joins.

Here is the code to list employees who work in the Zurich shop:

SELECT name, 
       gender, 
       CONCAT('$', FORMAT(salary, 2)) AS 'Salary' 
FROM   employees,
       shops
WHERE  employees.shop_id = shops.shop_id
AND    shops.shop = 'Zurich'; 

The above query produces the following two records:

name

gender

Salary

Jon Simpson

m

$4,500.00

Barbara Breitenmoser

f

(NULL)

In the above query, we matched the entire shop value against a single, specific string. You can cast a wider net by combining comparison operators and/or functions. Here are some of the more common operators. For a full list, see the MySQL documentation:

Name

Description

AND, &&

Logical AND

BETWEEN ... AND ...

Check whether a value is within a range of values

=

Equal operator

>=

Greater than or equal operator

Greater than operator

IS NOT NULL

NOT NULL value test

IS NOT

Test a value against a boolean

IS NULL

NULL value test

IS

Test a value against a boolean

<=

Less than or equal operator

Less than operator

LIKE

Simple pattern matching

!=, <>

Not equal operator

NOT LIKE

Negation of simple pattern matching

NOT, !

Negates value

||, OR

Logical OR

XOR

Logical XOR

SELECT name, 
       gender, 
       CONCAT('$', FORMAT(salary, 2)) AS 'Salary' 
FROM   employees,
       shops
WHERE  employees.shop_id = shops.shop_id
AND    shops.shop Like 'Z*' 
AND    shops.shop Not Like 'Za*'
AND    shops.shop Not Like 'Ze*'
AND    shops.shop IS NOT NULL; 

That concludes our look at the basic syntax of the SELECT statement. Next time, I’ll be sharing words of warning regarding the dangers of AND/OR combining, as well as the concept called operator precedence.

» See All Articles by Columnist Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date