SQL – Select data from two tables with INNER JOIN

On the first line, we specify the columns required from our tables after the SELECT clause.  Next, we list out two tables after the FROM keyword clause, separated by INNER JOIN.  The ON clause is then used to specify which columns are used to link the tables.

SELECT custnum, surname, dob, balance
 FROM customer INNER JOIN account 
 ON custnum = acccust 
 WHERE accstatus = 'LIVE'

SQL – Select data from two tables with the WHERE clause

On the first line, we specify the columns required from our tables after the SELECT clause.  Next, we list out two tables after the FROM keyword clause, separated by a comma.  The WHERE clause is then used to specify which columns are used to link the tables and any additional selection criteria.

SELECT custnum, surname, dob, balance
 FROM customer, account 
 WHERE custnum = acccust AND
       accstatus = 'LIVE'

SQL – Select data from two tables with the LEFT OUTER JOIN clause

The LEFT OUTER JOIN can be used to show rows from a table regardless of whether the match with the other table was successful.  Our example shows all customers and their accounts, if present.

On the first line, we specify the columns required from our tables after the SELECT clause.  Next, we list out two tables after the FROM keyword clause, separated by a LEFT OUTER JOIN clause.  The ON clause is then used to specify which columns are used to link the tables. Additional selection criteria are specified with the WHERE clause.

SELECT custnum, surname, dob, account, balance
 FROM customer LEFT OUTER JOIN account 
 ON custnum = acccust 
 WHERE dob > 19500101

The RIGHT OUTER JOIN clause below returns identical results.

SELECT custnum, surname, dob, account, balance
 FROM account RIGHT OUTER JOIN customer
 ON custnum = acccust 
 WHERE dob > 19500101

SQL – Select using LIKE Operators

Create a unique list of cities which begin with the letter A.

SELECT DISTINCT city
 FROM prodlib.customer
 WHERE city LIKE 'A%'
 ORDER BY city;

SQL – Select using BETWEEN Operator

Select customers born in the 1960s.

SELECT surname, city
FROM prodlib.customer
WHERE dob BETWEEN 19600101 AND 19691231

SQL – Select using IN Operators

Select customers who don’t live in London, Cardiff, Glasgow or Belfast.

SELECT surname, city
FROM prodlib.customer
AND NOT city IN ('London', 'Cardiff', 'Glasgow', 'Belfast')

SQL – Limit number of selected rows

Limit the number of results returned by a SELECT statement

SELECT firstname, surname
FROM prodlib.customer
FETCH FIRST 15 ROWS ONLY