SQL NULL Values

Toggle navigation
TUTORIAL HOME
SQL NULL Values
❮ Previous Next ❯
What is a NULL Value?
A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

Note: It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

How to Test for NULL Values?
It is not possible to test for NULL values with comparison operators, such as =, <, or .

We will have to use the IS NULL and IS NOT NULL operators instead.

IS NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
Demo Database
Assume we have the following “Persons” table:

ID LastName FirstName Address City
1 Doe John 542 W. 27th Street New York
2 Bloggs Joe London
3 Roe Jane New York
4 Smith John 110 Bishopsgate London
Suppose that the “Address” column in the “Persons” table is optional. If a record is inserted with no value for “Address”, the “Address” column will be saved with a NULL value.

The IS NULL Operator
The following SQL statement uses the IS NULL operator to list all persons that have no address:

SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NULL;
The result-set will look like this:

LastName FirstName Address
Bloggs Joe
Roe Jane
Tip: Always use IS NULL to look for NULL values.

The IS NOT NULL Operator
The following SQL statement uses the IS NOT NULL operator to list all persons that do have an address:

SELECT LastName, FirstName, Address FROM Persons
WHERE Address IS NOT NULL;
The result-set will look like this:

LastName FirstName Address
Doe John 542 W. 27th Street
Smith John 110 Bishopsgate

❮ Previous Next ❯

SQL AND, OR and NOT Operators

TUTORIAL HOME
SQL AND, OR and NOT Operators
❮ Previous Next ❯
The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND is TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
OR Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;
NOT Syntax
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
AND Example
The following SQL statement selects all fields from “Customers” where country is “Germany” AND city is “Berlin”:

Example
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;
»
OR Example
The following SQL statement selects all fields from “Customers” where city is “Berlin” OR “München”:

Example
SELECT * FROM Customers
WHERE City=’Berlin’ OR City=’München’;
»
NOT Example
The following SQL statement selects all fields from “Customers” where country is NOT “Germany”:

Example
SELECT * FROM Customers
WHERE NOT Country=’Germany’;
»
Combining AND, OR and NOT
You can also combine the AND, OR and NOT operators.

The following SQL statement selects all fields from “Customers” where country is “Germany” AND city must be “Berlin” OR “München” (use parenthesis to form complex expressions):

Example
SELECT * FROM Customers
WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
»
The following SQL statement selects all fields from “Customers” where country is NOT “Germany” and NOT “USA”:

Example
SELECT * FROM Customers
WHERE NOT Country=’Germany’ AND NOT Country=’USA’;
»

❮ Previous Next ❯

SQL ORDER BY Keyword

Toggle navigation
TUTORIAL HOME
SQL ORDER BY Keyword
❮ Previous Next ❯
The SQL ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set in ascending or descending order.

The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

ORDER BY Syntax
SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
ORDER BY Example
The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” column:

Example
SELECT * FROM Customers
ORDER BY Country;
»
ORDER BY DESC Example
The following SQL statement selects all customers from the “Customers” table, sorted DESCENDING by the “Country” column:

Example
SELECT * FROM Customers
ORDER BY Country DESC;
»
ORDER BY Several Columns Example
The following SQL statement selects all customers from the “Customers” table, sorted by the “Country” and the “CustomerName” column:

Example
SELECT * FROM Customers
ORDER BY Country, CustomerName;
»
ORDER BY Several Columns Example 2
The following SQL statement selects all customers from the “Customers” table, sorted ascending by the “Country” and descending by the “CustomerName” column:

Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
»

❮ Previous Next ❯

SQL WHERE Clause

Toggle navigation
TUTORIAL HOME
SQL WHERE Clause
❮ Previous Next ❯
The SQL WHERE Clause
The WHERE clause is used to filter records.

The WHERE clause is used to extract only those records that fulfill a specified condition.

WHERE Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement, etc.!

Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
WHERE Clause Example
The following SQL statement selects all the customers from the country “Mexico”, in the “Customers” table:

Example
SELECT * FROM Customers
WHERE Country=’Mexico’;
»
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

Example
SELECT * FROM Customers
WHERE CustomerID=1;
»
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:

Operator Description
= Equal
Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

❮ Previous Next ❯

SQL SELECT DISTINCT Statement

Toggle navigation
TUTORIAL HOME
SQL SELECT DISTINCT Statement
❮ Previous Next ❯
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

The SELECT DISTINCT statement is used to return only distinct (different) values.

SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, …
FROM table_name;
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
SELECT Example
The following SQL statement selects all (and duplicate) values from the “Country” column in the “Customers” table:

Example
SELECT Country FROM Customers;
»
Now, let us use the DISTINCT keyword with the above SELECT statement and see the result.

SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the “Country” column in the “Customers” table:

Example
SELECT DISTINCT Country FROM Customers;
»
The following SQL statement lists the number of different (distinct) customer countries:

Example
SELECT COUNT(DISTINCT Country) FROM Customers;
»
Note: The example above will not work in Firefox and Microsoft Edge! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access in our examples.

Here is the workaround for MS Access:

Example
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
»

❮ Previous Next ❯

SQL Syntax

Toggle navigation
TUTORIAL HOME
SQL Syntax
❮ Previous Next ❯
Database Tables
A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

In this tutorial we will use the well-known Northwind sample database (included in MS Access and MS SQL Server).

Below is a selection from the “Customers” table:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
The table above contains five records (one for each customer) and seven columns (CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).

SQL Statements
Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement selects all the records in the “Customers” table:

Example
SELECT * FROM Customers;
»
In this tutorial we will teach you all about the different SQL statements.

Keep in Mind That…
SQL keywords are NOT case sensitive: select is the same as SELECT
In this tutorial we will write all SQL keywords in upper-case.

Semicolon after SQL Statements?
Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

In this tutorial, we will use semicolon at the end of each SQL statement.

Some of The Most Important SQL Commands
SELECT – extracts data from a database
UPDATE – updates data in a database
DELETE – deletes data from a database
INSERT INTO – inserts new data into a database
CREATE DATABASE – creates a new database
ALTER DATABASE – modifies a database
CREATE TABLE – creates a new table
ALTER TABLE – modifies a table
DROP TABLE – deletes a table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index

❮ Previous Next ❯

SQL SELECT Statement

Toggle navigation
TUTORIAL HOME
SQL SELECT Statement
❮ Previous Next ❯
The SQL SELECT Statement
The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax
SELECT column1, column2, …
FROM table_name;
Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;
Demo Database
Below is a selection from the “Customers” table in the Northwind sample database:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
SELECT Column Example
The following SQL statement selects the “CustomerName” and “City” columns from the “Customers” table:

Example
SELECT CustomerName, City FROM Customers;
»
SELECT * Example
The following SQL statement selects all the columns from the “Customers” table:

Example
SELECT * FROM Customers;
»

❮ Previous Next ❯

Introduction to SQL

Toggle navigation
TUTORIAL HOME
Introduction to SQL
❮ Previous Next ❯
SQL is a standard language for accessing and manipulating databases.

What is SQL?
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL is an ANSI (American National Standards Institute) standard
What Can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
SQL is a Standard – BUT….
Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the SQL language.

However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!

Using SQL in Your Web Site
To build a web site that shows data from a database, you will need:

An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
To use a server-side scripting language, like PHP or ASP
To use SQL to get the data you want
To use HTML / CSS to style the page
RDBMS
RDBMS stands for Relational Database Management System.

RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Look at the “Customers” table:

Example
SELECT * FROM Customers;
»
Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City and PostalCode. A field is a column in a table that is designed to maintain specific information about every record in the table.

A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

❮ Previous Next ❯