Some notes on SQL: 2 - Basic SELECT

Part 1 of this sequence of blog posts provided a preamble and showed how to create databases. This post introduces the basic SELECT command, which shows you what lies within your database and as it's name implies allows you to select only parts of the data contained within.

The basic form of SELECT is:

SELECT * FROM my_contacts
WHERE first_name = 'Anne';

* indicates that all fields should be returned from the table 'my_contacts', where the first_name field = 'Anne'. We don't have to take all the fields from a table:

SELECT first_name, last_name, email FROM my_contacts
WHERE first_name = 'Anne';

As well as the equivalence operator =, we can also use comparison operators <> (not equal), <, >, <=, >= these work not only with numerical values, but also with text values. WHERE clauses can also be combined with AND and OR operators.

SELECT drink_name FROM drink_info
WHERE calories >= 30
AND calories <= 60;


SELECT drink_name FROM drink_info
WHERE calories BETWEEN 30 AND 60;

The second select using the BETWEEN keyword is equivalent to the first.
In addition there are wildcards, % meaning 'any number of characters' and _ meaning 'one character' which are accessed via the LIKE keyword:

SELECT first_name FROM my_contacts
WHERE first_name LIKE '%im';

This first search will return 'Tim', 'Slim', and 'Ephraim'.

SELECT first_name FROM my_contacts
WHERE first_name LIKE '_im';

This second search will only return 'Tim'. NULL is special, nothing equals NULL but you can check if something is NULL:

SELECT first_name FROM my_contacts WHERE flag IS NULL;

Comparisons can be made to a list with the IN keyword:

SELECT drink_name FROM drink_info
WHERE rating IN ( 'good', 'excellent', 'average' );

Finally, the NOT operator can be used to find the inverse of the selection made, the NOT keyword goes directly after IN but otherwise goes after WHERE:

SELECT drink_name FROM drink_info
WHERE rating NOT IN ( 'good', 'excellent', 'average' );


SELECT first_name FROM my_contacts
WHERE NOT first_name LIKE '_im';

Keywords: AND, OR, BETWEEN, IS NULL, NOT, LIKE, IN