SQL - NOT NULL Constraint

By default, a column can hold NULL values.

The NOT NULL constraint enforces a column to NOT accept NULL values.

This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values:

Example

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

Try it Yourself »

Tip: If the table has already been created, you can add a NOT NULL constraint to a column with the ALTER TABLE statement.

SQL - Date Functions

The following table lists the most important built-in date functions in MySQL:

Function	Description
NOW()	Returns the current date and time
CURDATE()	Returns the current date
CURTIME()	Returns the current time
DATE()	Extracts the date part of a date or date/time expression
EXTRACT()	Returns a single part of a date/time
DATE_ADD()	Adds a specified time interval to a date
DATE_SUB()	Subtracts a specified time interval from a date
DATEDIFF()	Returns the number of days between two dates
DATE_FORMAT()	Displays date/time data in different formats

SQL - Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Function	Description
AVG()	Returns the average value
COUNT()	Returns the number of rows
FIRST()	Returns the first value
LAST()	Returns the last value
MAX()	Returns the largest value
MIN()	Returns the smallest value
ROUND()	Rounds a numeric field to the number of decimals specified
SUM()	Returns the sum

SQL - INSERT INTO SELECT Examples

Copy only a few columns from "Suppliers" into "Customers":

Example

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

Try it Yourself »

Copy only the German suppliers into "Customers":

Example

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='India';

Try it Yourself »

SQL - INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

SQL - SELECT INTO Examples

Create a backup copy of Customers:

SELECT *
INTO CustomersBackup2017
FROM Customers;

Use the IN clause to copy the table into another database:

SELECT *
INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

Copy only a few columns into the new table:

SELECT CustomerName, ContactName
INTO CustomersBackup2017
FROM Customers;

Copy only the German customers into the new table:

SELECT *
INTO CustomersBackup2017
FROM Customers
WHERE Country='India';

Copy data from more than one table into the new table:

SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;

Tip: The SELECT INTO statement can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT *
INTO newtable
FROM table1
WHERE 1=0;

SQL - SELECT INTO Syntax

We can copy all columns into the new table:

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Or we can copy only the columns we want into the new table:

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;

The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

SQL - UNION ALL With WHERE

The following SQL statement uses UNION ALL to select all (duplicate values also) German cities from the "Customers" and "Suppliers" tables:

Example

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Try it Yourself »

SQL - UNION ALL Example

The following SQL statement uses UNION ALL to select all (duplicate values also) cities from the "Customers" and "Suppliers" tables:

Example

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

Try it Yourself »

SQL - UNION Example

The following SQL statement selects all the different cities (only distinct values) from the "Customers" and the "Suppliers" tables:

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Try it Yourself »

Note: UNION cannot be used to list ALL cities from the two tables. If several customers and suppliers share the same city, each city will only be listed once. UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL - UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Note : The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.

SQL - UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SQL - INNER JOIN Example

The following SQL statement will return all customers with orders:

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Try it Yourself »

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a 

match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.

SQL - INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

PS! INNER JOIN is the same as JOIN.

Different SQL JOINs

Before we continue with examples, we will list the types of the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables.

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table.

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.

• FULL JOIN: Return all rows when there is a match in ONE of the tables.

SQL - NOT BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';

Try it Yourself »

SQL - BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';

Try it Yourself »

SQL - BETWEEN Operator with IN Example

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

Example

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

Try it Yourself »

SQL - NOT BETWEEN Operator Example

To display the products outside the range of the previous example, use NOT BETWEEN:

Example

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

Try it Yourself »

SQL - BETWEEN Operator Example

The following SQL statement selects all products with a price BETWEEN 10 and 20:

Example

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

Try it Yourself »