News & UpdatesProgrammingWeb programming Store My Projects
Links
Affiliates

SQL Tutorial – 06 – Select

The SELECT statement is the most frequently used SQL operation and it retrieves rows from a table. These retrieved rows are known as the result-set. As previously seen, the SELECT statement below retrieves all rows and columns. The asterisk may optionally be left out.

SELECT * FROM mytable;
SELECT FROM mytable;

The asterisk can be replaced with one or more specific columns to retrieve.

SELECT name, id FROM mytable;

Where

The WHERE clause can be added to the SELECT statement to specify a selection condition.

SELECT name, id FROM mytable WHERE id = 1;

The regular conditional operators can be used with the WHERE clause, as well as some operators that are more specific to SQL.

OperatorDescription
=Equal
<>Not Equal
>Greater than
<Less than
>=Greater than or Equal
<=Less than or Equal
ANDLogical AND
ORLogical OR
NOTLogical NOT
INMatches a list of values
LIKEMatches against a pattern
BETWEEN ANDMatches values between a range

The operators can be applied to strings and dates, just as well as numbers. Note that the standard way to type strings and dates in SQL is using single quotes, but most database systems will also accept double quotes.

SELECT name, id FROM mytable WHERE name > 'Jill';

The logical operators – AND/OR – can be used to apply several selection conditions.

SELECT name, id FROM mytable WHERE id > 1 AND NOT id = 6;

Parenthesis can be used to override the default operator precedence. This helps to clarify the condition below.

SELECT name, id FROM mytable WHERE id = 1 OR (id > 2 AND id < 5);

The IN operator lets you list values that must be in a column in order to be selected. It has the same meaning as listing the conditions separately with the OR operator.

SELECT name, id FROM mytable WHERE name IN ('Alex', 'Matt');
SELECT name, id FROM mytable WHERE name = 'Alex' OR name = 'Matt';

The LIKE operator specifies a search pattern for a column using wildcards. The percentage sign (%) selects multiple characters, so the first example below will select all names containing the letter “a”. There is also the underscore wildcard (_) that matches a single character. The second example below uses this type of wildcard and will select names that have exactly six letters.

SELECT name, id FROM mytable WHERE name LIKE '%a%';
SELECT name, id FROM mytable WHERE name LIKE '______';

The last operator is BETWEEN AND that selects a range of data between two values.

SELECT name, id FROM mytable WHERE id BETWEEN 3 AND 5;

Distinct

If there are several rows with the same value for a column, you can add the DISTINCT keyword to only select values that are different.

SELECT DISTINCT name FROM mytable;

Limit

The LIMIT modifier allows you to limit the number of rows that are selected.

SELECT name, id FROM mytable LIMIT 2;

LIMIT can also be used to specify an interval of rows to be retrieved. For example, the statement below will exclude the first 2 rows and retrieve only the next 3.

SELECT name, id FROM mytable LIMIT 2,3;

Order by modifier

The ORDER BY keyword is used to sort the retrieved rows. By default, the rows will be ordered alphabetically, or ascending. This can be specified explicitly using the ASC keyword. The alternative is to order the rows in reverse alphabetical order, or descending, using the DESC keyword. Rows can be sorted by more than one column.

SELECT name, id FROM mytable ORDER BY name ASC;
SELECT name, id FROM mytable ORDER BY name ASC, id DESC;

Writing to and reading from a file

To write data from a table to a file there’s the SELECT INTO OUTFILE statement. Loading the file back into a table is done using the LOAD DATA INFILE statement.

SELECT * INTO OUTFILE 'c:/results.txt' FROM mytable;
LOAD DATA INFILE 'c:/results.txt' INTO TABLE mytable;
Recommended additional reading:
Sams - Teach Yourself SQL in 10 Minutes