SQL Commands: A List with SQL Syntax

SQL, or Structured Query Language, contains a range of commands for interacting with the database. This article lists all of those commands, what they do, and what they look like.

All of these basic SQL commands work on Oracle, SQL Server, MySQL, and PostgreSQL, unless otherwise indicated. The reason that all commands don't work on all databases is because some commands are not part of the SQL standard and the database vendors have chosen not to implement them.

Let's take a look at the SQL commands and the SQL syntax for them.

SELECT

The SELECT command is used to read data from the database. It specifies the columns to be displayed, as well as any expressions or calculations to be displayed.

1SELECT columns
2...

For example:

1SELECT first_name, last_name
2FROM customer;

In many SQL vendors, the FROM clause is required when writing a SELECT query, and in other vendors it is optional.

FROM

The FROM command is used to specify which table or view the data will be selected from as part of a SELECT query. The FROM clause is often a table but it can also be a view or a subquery.

It looks like this:

1FROM table_name

For example:

1SELECT first_name, last_name
2FROM customer;

It can also be used with a subquery, which is the part in brackets below:

1SELECT first_name, last_name
2FROM (
3   SELECT ...
4);

WHERE

The WHERE clause is used in SQL to filter or restrict the rows that are impacted. It's used in the SELECT, UPDATE, and DELETE statements. You specify the conditions that must be true for a row to be impacted by the statement.

1WHERE condition

For example:

1SELECT first_name, last_name
2FROM customer
3WHERE status = 'Active';

This query will show all customers where their status value is equal to "Active". If a customer row is not equal to "Active", it won't be shown.

GROUP BY

The GROUP BY SQL command is used in a SELECT query to combine or aggregate data into groups. It's helpful if you want to use an aggregate function (e.g. COUNT) and show the value for each occurrence of another column.

1GROUP BY condition

For example:

1SELECT first_name, COUNT(*)
2FROM customer
3GROUP BY first_name;

This query will show all of the first_name values and the count of those values in the customer table.

HAVING

The HAVING command is used along with a GROUP BY command to filter the results of a SELECT query after it has been grouped. It's similar to WHERE, but WHERE works before the grouping and HAVING works after the grouping.

1HAVING condition

For example:

1SELECT first_name, COUNT(*)
2FROM customer
3GROUP BY first_name
4HAVING COUNT(*) > 1;

This will show all first_name values, their count of occurrences, for those that have a count greater than 1.

ORDER BY

The ORDER BY command in SQL is used to specify the order that the results of a SELECT query should be shown in. The ORDER BY clause can order by one or more columns and can order in either ascending or descending order.

1ORDER BY column [ASC|DESC] [,column...]

For example:

1SELECT first_name, last_name
2FROM customer
3ORDER BY first_name ASC;

This will show the first and last names of customers ordered by the first name in ascending order.

INNER JOIN

An Inner Join is a type of join where the data in two tables is joined together, and rows that match in both tables are shown.

1FROM table1 INNER JOIN table2 ON criteria

For example:

1SELECT order.order_id,
2customer.first_name
3FROM customer
4INNER JOIN order ON customer.customer_id = order.customer_id;

This query will show all order IDs and customer first_names where there is a match in both tables.

More information on joins (all join types) can be found in this guide to SQL joins.

You can use more than one join too: check out this post for more information: How to Use Multiple Inner Joins in SQL.

LEFT JOIN/LEFT OUTER JOIN

A Left Join, which can also be called a Left Outer Join, is a type of join where two tables are joined together. However, the table on the left of the JOIN keyword has all records shown, and if there are any matches in the table on the right, they are also shown, otherwise NULL values are shown.

1FROM table1 LEFT JOIN table2 ON criteria

For example:

1SELECT order.order_id,
2customer.first_name
3FROM customer
4LEFT JOIN order ON customer.customer_id = order.customer_id;

This query will show all customer first names, and if they have an order, the order IDs are shown. If they don't have an order, then a NULL value is shown.

RIGHT JOIN/RIGHT OUTER JOIN

A Right Join, which can also be called a Right Outer Join, is a type of join where two tables are joined together. However, the table on the right of the JOIN keyword has all records shown, and if there are any matches in the table on the left, they are also shown, otherwise NULL values are shown.

1FROM table1 RIGHT JOIN table2 ON criteria

For example:

1SELECT order.order_id,
2customer.first_name
3FROM customer
4RIGHT JOIN order ON customer.customer_id = order.customer_id;

This query will show all orders, and if there is a related customer, the first name is shown. If the order does not have a customer, then a NULL value is shown.

FULL JOIN/FULL OUTER JOIN

(Not Available in MySQL)

A Full Join or a Full Outer Join is a combination of a Left Join and a Right Join.

It shows matching rows between two tables, and if there are no matches in one table for the row in the other table, a NULL value is shown.

I've written more about joins here.

A Full Join looks like this:

1FROM table1 FULL OUTER JOIN table2 ON criteria

For example:

1SELECT order.order_id,
2customer.first_name
3FROM customer
4FULL OUTER JOIN order ON customer.customer_id = order.customer_id;

This query will show all orders and all customers, showing matches where rows exist in both tables, and NULL values for rows that don't exist in the opposing table.

AS

The AS keyword allows you to provide an alias to either a column or a table.

Providing an alias using the AS keyword to a column will mean it uses the alias as the column header, which may be more readable:

1SELECT COUNT(first_name) AS count_fn
2FROM customer;

Providing an alias to a table means that the alias can be used in the query instead of the full table name:

1SELECT o.order_id,
2c.first_name
3FROM customer AS c
4INNER JOIN order AS o ON c.customer_id = o.customer_id;

DISTINCT

The DISTINCT keyword is used in SELECT statements to remove duplicate rows from the result.

If a row is duplicated in the results of the SELECT query, adding DISTINCT will ensure only one is shown and duplicates are not shown.

It must be added immediately after the SELECT keyword:

1SELECT DISTINCT first_name, last_name
2FROM customer;

LIKE

The LIKE keyword is used to perform wildcard searches, or partial matches, on string values in a WHERE clause.

This can help with searching for values that contain other values or start with other values.

1WHERE condition LIKE criteria

For example:

1SELECT COUNT(*)
2FROM customer
3WHERE first_name LIKE 'A%';

This query will show the number of records where the first name starts with the letter "A".

AND

The AND keyword allows you to use two conditions in a WHERE clause and specify that both must be true.

1WHERE criteria1 AND criteria2 [AND criteria_n]

You can use as many AND keywords and criteria as necessary.

For example:

1SELECT first_name, last_name
2FROM customer
3WHERE status = 'Active'
4AND region = 'North';

This query shows the customer first and last names where the status is "Active" and the region is "North".

OR

The OR keyword allows you to use two conditions in a WHERE clause and specify that at least one must be true.

1WHERE criteria1 OR criteria2 [OR criteria_n]

You can use as many OR keywords and criteria as necessary.

For example:

1SELECT first_name, last_name
2FROM customer
3WHERE status = 'Active'
4OR status = 'Pending';

This query shows the customer first and last names where the status is "Active" or "Pending".

BETWEEN

The BETWEEN keyword in SQL is used in a WHERE clause to specify a value must be within a specified range of values.

1WHERE expression BETWEEN min_value AND max_value

The values in the BETWEEN clause are inclusive.

For example:

1SELECT order_id
2FROM orders
3WHERE order_amount BETWEEN 100 AND 200;

This query will show the order IDs of orders where the order_amount is between 100 and 200. If an order is 100 it is included. Order amounts of 200 are also included.

EXISTS

The EXISTS keyword lets you check if a record is found within a subquery. If it is found. it can be returned to the main query. It can be used where subqueries are used, such as SELECT, INSERT, UPDATE, and DELETE statements.

1WHERE EXISTS (SELECT ... );

For example:

1SELECT city_name
2FROM cities
3WHERE EXISTS (
4  SELECT id
5  FROM olympics
6  WHERE olympics.city_name = cities.city_name
7);

This query will display a list of city names that exist in the subquery of Olympic city names.

IN

The IN keyword allows you to check a list of values, or a subquery, to see if it matches a column or expression.

1WHERE expression IN (list_of_values)

For example, it can be done with specific values:

1SELECT COUNT(*)
2FROM customer
3WHERE status IN ('Active', 'Pending', 'Paused');

Or, it can be done with a subquery:

1SELECT COUNT(*)
2FROM customer
3WHERE status IN (
4  SELECT status_value
5  FROM status_lookup
6);

These queries look up customers that have specific status values.

UNION

The UNION keyword allows you to combine the results of two queries. The data type and number of columns must match.

1SELECT columns
2FROM table1
3UNION
4SELECT columns
5FROM table2

For example:

1SELECT first_name, last_name
2FROM customer
3UNION
4SELECT first_name, last_name
5FROM employee;

This query will show one list of results that have first names and last names for both customers and employees.

UNION ALL

The UNION ALL keyword allows you to combine the results of two queries. The data type and the number of columns must match.

It's different to UNION because UNION removes duplicate values and UNION ALL does not.

1SELECT columns
2FROM table1
3UNION ALL
4SELECT columns
5FROM table2

For example:

1SELECT first_name, last_name
2FROM customer
3UNION ALL
4SELECT first_name, last_name
5FROM employee;

This query will show one list of results that have first names and last names for both customers and employees.

LIMIT

(Not Available in Oracle or SQL Server)

The LIMIT keyword lets you specify the maximum number of rows to be returned in your result set.

In MySQL and PostgreSQL, you can use this LIMIT keyword. In Oracle, you can use the ROWNUMBER feature, and in SQL Server you can use the TOP keyword.

The LIMIT feature works like this:

1SELECT columns
2FROM table
3LIMIT number;

For example:

1SELECT first_name, last_name
2FROM customer
3LIMIT 10;

This query will select the first 10 rows from the customer table. It's often used with an ORDER BY clause to put the results in a certain order.

WITH

The WITH clause in SQL allows you to define a name for a subquery, and use that subquery with its name in the main query. It allows you to clean up your query to make it more readable, and may offer a performance enhancement.

It's also known as a Common Table Expression (CTE) or subquery refactoring.

1WITH subquery_name AS (
2  SELECT columns
3  FROM table
4)
5SELECT columns
6FROM subquery_name...;

For example:

1WITH olympic_cities AS (
2  SELECT city_id, city_name
3  FROM olympics
4)
5SELECT olympic_cities.city_id,
6olympic_cities.city_name,
7cities.population
8FROM olympic_cities
9INNER JOIN cities ON olympic_cities.city_id = cities.city_id;

This query will show details about Olympic cities such as their names and populations.

UPDATE

The UPDATE command is a basic SQL command and is used to make changes to data that exists in a table. There is no need to remove and re-add the data.

The WHERE clause is optional.

1UPDATE table
2SET column = value
3[WHERE criteria];

For example:

1UPDATE customer
2SET status = 'Inactive'
3WHERE customer_id = 4;

This query will update the status to "Inactive" for the row in the customer table where the customer ID is 4.

It can also be used to update data based on data in another table.

DELETE

The DELETE keyword is used to delete or remove a record from a table.

1DELETE FROM table
2[WHERE criteria];

For example:

1DELETE FROM customer
2WHERE customer_id = 4;

This query will delete the records in the customer table where the customer_id is 4.

INSERT

The INSERT command will add a new record to a table. It can be used to add multiple records to a table.

1INSERT INTO table (columns) VALUES (values);

For example:

1INSERT INTO customer (customer_id, first_name, last_name)
2VALUES (15, 'John', 'Smith');

This statement will add a new record to the customer table.

TRUNCATE

The TRUNCATE statement is used to delete all of the data in a table. It's an easy method to remove all data from a table. You can't specify a WHERE clause to only delete some data. It's also often quicker than a DELETE statement.

1TRUNCATE TABLE tablename;

For example:

1TRUNCATE TABLE customer;

This query will truncate the customer table, removing all data.

CREATE TABLE

The CREATE TABLE statement is used to create a new table in the database.

Different databases have different parameters that can be added to the statement, but all of them allow you to specify a name as well as columns.

1CREATE TABLE tablename (
2  columns
3);

For example:

1CREATE TABLE employee (
2  employee_id INT PRIMARY KEY,
3  first_name VARCHAR(200),
4  last_name VARCHAR(200)
5);

This statement will create a new table called employee.

ALTER TABLE

The ALTER TABLE statement will let you make changes to a table that already exists. Many things can be done with an Alter Table statement, such as adding and removing columns or renaming the table.

1ALTER TABLE tablename operation;

For example:

1ALTER TABLE customer ADD customer_type VARCHAR(20);

This statement will add a new column called customer_type to the customer table.

CREATE DATABASE

(Not available in Oracle)

The CREATE DATABASE command lets you create a new database on the system. A database can be used to contain tables, procedures, and other database objects.

1CREATE DATABASE database_name;

For example:

1CREATE DATABASE movies;

This statement will create a new database called movies.

CREATE INDEX

The CREATE INDEX statement is used to create a new index on the database. An index is an object that stores a reference to rows in a table, to help speed up queries that refer to the table's data.

1CREATE INDEX index_name ON index_details;

For example:

1CREATE INDEX idx_cust_firstname ON customer(first_name);

This command will create a new index called idx_cust_firstname on the first_name column in the customer table.

CREATE VIEW

The CREATE VIEW command lets you create a new view object, which is an SQL query that is named and saved to the database. You can query from the view just like a normal table.

1CREATE VIEW view_name AS SELECT ...;

For example:

1CREATE VIEW olympic_cities AS
2SELECT id, city_name, population
3FROM cities
4INNER JOIN olympics ON cities.id = olympics.city_id;

This query creates a new view called olympic_cities which refers to a result from the cities and olympics tables.

DROP DATABASE

(Not available in Oracle)

The DROP DATABASE command will remove the specified database from the system, and all objects included in the database.

1DROP DATABASE database_name;

For example:

1DROP DATABASE movies;

This query will drop the database called movies from the system.

DROP INDEX

The DROP INDEX command is used to remove an index from the database.

1DROP INDEX index_name;

The syntax depends on which database vendor is used.

SQL Server

1DROP INDEX table_name.index_name;

Oracle, PostgreSQL

1DROP INDEX index_name;

MySQL

1ALTER TABLE table_name DROP INDEX index_name;

MySQL does not have a DROP INDEX command as this feature is part of the ALTER TABLE command.

DROP TABLE

The DROP TABLE command is used to remove a table from the database.

1DROP TABLE table_name;

For example:

1DROP TABLE customer;

This command will drop the customer table from the database.

DROP VIEW

The DROP VIEW command will remove or drop a view from the system.

1DROP VIEW view_name;

For example:

1DROP VIEW olympic_cities;

This query will drop the view called olympic_cities from the database.

GRANT

The GRANT command will allow you to give a user a certain privilege or permission on the database.

1GRANT privilege TO user;

For example:

1GRANT CREATE TABLE TO ben;

This command will grant a privilege called CREATE TABLE to a user called "ben";

REVOKE

The REVOKE command will remove a privilege or permission from a specified user.

1REVOKE privilege FROM user;

For example:

1REVOKE UPDATE ON cities FROM ben;

This command will revoke or remove the update privilege on the cities table from the user "ben".

COMMIT

The COMMIT command is used to save the changes from your currently open transaction to the database. This will permanently store the changes, as depending on your database and IDE settings they may not be committed automatically.

1COMMIT;

There are no options for this command. Simply the word COMMIT will commit the transaction.

ROLLBACK

The ROLLBACK command is used to undo any changes made from your currently open transaction. Changes that are made are not applied to the database.

1ROLLBACK;

There are no options for this command. Simply the word ROLLBACK will roll back the transaction.

SAVEPOINT

The SAVEPOINT command lets you name a point in a transaction for you to roll back to at a later time if needed.

1SAVEPOINT savepoint_name;

For example:

1SAVEPOINT before_new_customer;

This statement creates a savepoint called "before_new_customer" which can be used to roll back to later in the transaction.

MySQL Commands

Here's a list of MySQL commands that you can use in addition to those above. These commands are specific to MySQL.

So that's the full list of SQL commands along with the SQL syntax and examples of each of them.

Do you have any questions on these commands? If so, let me know in the comments section below.