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.

SELECT columns
...

For example:

SELECT first_name, last_name
FROM 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:

FROM table_name

For example:

SELECT first_name, last_name
FROM customer;

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

SELECT first_name, last_name
FROM (
   SELECT ...
);

 

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.

WHERE condition

For example:

SELECT first_name, last_name
FROM customer
WHERE 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.

GROUP BY condition

For example:

SELECT first_name, COUNT(*)
FROM customer
GROUP 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.

HAVING condition

For example:

SELECT first_name, COUNT(*)
FROM customer
GROUP BY first_name
HAVING 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.

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

For example:

SELECT first_name, last_name
FROM customer
ORDER 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.

FROM table1 INNER JOIN table2 ON criteria

For example:

SELECT order.order_id,
customer.first_name
FROM customer
INNER 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.

FROM table1 LEFT JOIN table2 ON criteria

For example:

SELECT order.order_id,
customer.first_name
FROM customer
LEFT 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.

FROM table1 RIGHT JOIN table2 ON criteria

For example:

SELECT order.order_id,
customer.first_name
FROM customer
RIGHT 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:

FROM table1 FULL OUTER JOIN table2 ON criteria

For example:

SELECT order.order_id,
customer.first_name
FROM customer
FULL 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:

SELECT COUNT(first_name) AS count_fn
FROM customer;

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

SELECT o.order_id,
c.first_name
FROM customer AS c
INNER 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:

SELECT DISTINCT first_name, last_name
FROM 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.

WHERE condition LIKE criteria

For example:

SELECT COUNT(*)
FROM customer
WHERE 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.

WHERE criteria1 AND criteria2 [AND criteria_n]

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

For example:

SELECT first_name, last_name
FROM customer
WHERE status = 'Active'
AND 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.

WHERE criteria1 OR criteria2 [OR criteria_n]

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

For example:

SELECT first_name, last_name
FROM customer
WHERE status = 'Active'
OR 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.

WHERE expression BETWEEN min_value AND max_value

The values in the BETWEEN clause are inclusive.

For example:

SELECT order_id
FROM orders
WHERE 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.

WHERE EXISTS (SELECT ... );

For example:

SELECT city_name
FROM cities
WHERE EXISTS (
  SELECT id
  FROM olympics
  WHERE olympics.city_name = cities.city_name
);

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.

WHERE expression IN (list_of_values)

For example, it can be done with specific values:

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

Or, it can be done with a subquery:

SELECT COUNT(*)
FROM customer
WHERE status IN (
  SELECT status_value
  FROM status_lookup
);

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.

SELECT columns
FROM table1
UNION
SELECT columns
FROM table2

For example:

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM 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.

SELECT columns
FROM table1
UNION ALL
SELECT columns
FROM table2

For example:

SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM 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:

SELECT columns
FROM table
LIMIT number;

For example:

SELECT first_name, last_name
FROM customer
LIMIT 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.

WITH subquery_name AS (
  SELECT columns
  FROM table
)
SELECT columns
FROM subquery_name...;

For example:

WITH olympic_cities AS (
  SELECT city_id, city_name
  FROM olympics
)
SELECT olympic_cities.city_id,
olympic_cities.city_name,
cities.population
FROM olympic_cities
INNER 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.

UPDATE table
SET column = value
[WHERE criteria];

For example:

UPDATE customer
SET status = 'Inactive'
WHERE 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.

DELETE FROM table
[WHERE criteria];

For example:

DELETE FROM customer
WHERE 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.

INSERT INTO table (columns) VALUES (values);

For example:

INSERT INTO customer (customer_id, first_name, last_name)
VALUES (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.

TRUNCATE TABLE tablename;

For example:

TRUNCATE 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.

CREATE TABLE tablename (
  columns
);

For example:

CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(200),
  last_name VARCHAR(200)
);

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.

ALTER TABLE tablename operation;

For example:

ALTER 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.

CREATE DATABASE database_name;

For example:

CREATE 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.

CREATE INDEX index_name ON index_details;

For example:

CREATE 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.

CREATE VIEW view_name AS SELECT ...;

For example:

CREATE VIEW olympic_cities AS
SELECT id, city_name, population
FROM cities
INNER 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.

DROP DATABASE database_name;

For example:

DROP 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.

DROP INDEX index_name;

The syntax depends on which database vendor is used.

SQL Server

DROP INDEX table_name.index_name;

Oracle, PostgreSQL

DROP INDEX index_name;

MySQL

ALTER 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.

DROP TABLE table_name;

For example:

DROP 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.

DROP VIEW view_name;

For example:

DROP 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.

GRANT privilege TO user;

For example:

GRANT 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.

REVOKE privilege FROM user;

For example:

REVOKE 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.

COMMIT;

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.

ROLLBACK;

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.

SAVEPOINT savepoint_name;

For example:

SAVEPOINT 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.

Command	Explanation
[mysql dir]/bin/mysql -h hostname -u username -p	Log in to MySQL from a command line. -h allows you to specify the hostname, -u allows you to specify the username. -p allows you to be prompted for the password
show databases;	Displays a list of databases
use database_name;	Sets the specified database name as the active one
show tables;	Display a list of all tables in the database
describe table_name;	Display a list of columns and their formats for a specific table. More information here: SQL Describe Table
show columns from table_name;	Displays a list of columns from a specific table
SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘newpassword’);	Updates the password for the specified user
LOAD DATA INFILE ‘/tmp/filename.csv’ REPLACE INTO TABLE table_name FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2, …);	Load a specified CSV file into a table

 

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.