Databases form the backbone of the modern web. Every big or dynamic website uses a database in some way, and when combined with SQL commands (Structured Query Language), the possibilities for manipulating data are really endless.

There are many names for data returned from a database table. Data is commonly referred to as Rows, Records, or Tuples. Each of these terms are used interchangeably throughout this article.

Preface

All the examples are based on four fictional tables. The customer table contains the name and age of customers:

Name

Age

Joe

99

James

78

Ryan

101

The heights table contains the name and height of any person:

Name

Height

Joe

101

James

102

Ryan

103

The staff table contains the name and age of staff members, exactly the same as the customer table:

Name

Age

Joe

17

James

24

Ryan

18

The final table people contains the name and age of people, just like the customer and staff tables:

Name

Age

Joe

98

James

99

Ryan

100

1. Select

The select command in SQL is the simplest, yet one of the most important SQL queries within the suite of SQL commands.

It's considered a best practice to write your reserved SQL syntax in uppercase, as it makes the select command easy to read and understand.

As its name implies, select is used to select data from a database. Here's the simplest usage:

        SELECT * FROM table;
    

There are two parts to this command line. The first part (SELECT *) specifies which columns you would like to select.

The asterisk indicates that you wish to select all the columns from the defined table. The second part (FROM table) tells your database engine where you would like to retrieve this data from.

Replace table with the name of your database table.

This select statement is known as select star. The asterisk is a good way to figure out what data is in a table, but it’s not always recommended for production codes.

Most of the time, you would be working with tons of rows of code—which means that your select * statement would put your system into limbo mode. This is because it tries to fetch all rows of data from the defined table.

When using a select star, it's up to the database engine to present you with the data you want. You don't have any control over the order the data is returned, so if somebody adds a new column to the table, you may find your variables in your programming language no longer represent the correct data. Fortunately, there is a solution.

You can explicitly state which columns you would like to retrieve, like this:

        SELECT age, name FROM people;
    
SQL interface

This query retrieves the age and name columns from the people table. Being this explicit can be slightly tedious if you have a lot of data, but doing so will reduce problems in the future, along with making your SQL query easier to understand.

If you want to select an additional piece of data, but it's not stored in any of your tables, you can do that like this:

        SELECT age, '1234' FROM people;
    
SQL interface

Any string inside single quotes will be returned instead of matching a column name.

Related: SQL vs. NoSQL: What's the Best Database for Your Next Project?

2. Where

The select command is excellent for retrieving data, but what if you want to filter the results based on certain criteria?

What about retrieving only people who have blue eyes? What about people born in January who work as mechanics?

This is where the where command comes in. The use of this SQL command allows you to apply conditions with the select statement, and you simply append it to the end of the statement:

        SELECT age, name FROM people WHERE age < 100
    
SQL interface

This query is now restricted to people who are below 100 years of age.

You can combine multiple conditions using the AND operator:

        SELECT * FROM customer WHERE age > 80 AND age < 100;
    
SQL interface

The AND command works exactly like it does in the English language: it applies another condition to the statement.

Another command that can be used in conjunction with this is OR. Here's an example:

        SELECT * FROM customer WHERE age > 10 OR name = 'Joe';
    
SQL interface

This query returns records where the age is greater than 90, or the name is equal to Joe.

3. Order

The order command is used to sort the results in ascending/descending order. Simply append it to the end of your statement, after the where statement, if you are using one:

        SELECT * FROM customer ORDER BY age DESC;
    
SQL interface

You need to specify the column and the order, which can be ASC for ascending or DESC for descending. If ASC or DESC is not defined, the sorting order will be ascending by default.

You can order by multiple columns like this:

select * from staff order by age desc, name;

SQL interface

ORDER BY is one of the most useful commands when combined with other commands. Not all queries will return data in a logical or ordered way; this command lets you change that.

4. Join

The join command in sql is used to join related data stored in one or more tables. You can join one table to another, to fetch combined results.

Here's a basic example:

        SELECT age, name, height FROM people 
LEFT JOIN heights
USING (name);
SQL interface

You have to start with the LEFT JOIN syntax, which specifies that you want to join a table using a join of type left.

Related: How to Query Multiple Database Tables at Once With SQL Joins

Next, specify the table you wish to join (heights). The USING (name) syntax states that the column name can be found in both tables, and this should be used as a primary key to join the tables together.

Don't worry if your columns have different names in each table. You can use ON instead of USING:

        SELECT a.age, a.name,b.Height FROM people a LEFT JOIN heights as b ON a.name = b.name;
    
SQL interface

The on statement explicitly states which columns to key on. There are many types of joins; here's a quick summary of their uses:

  • (INNER) JOIN returns rows with a match in both tables.
  • LEFT (OUTER) JOIN returns all the rows from the left table, with any matches from the right table. If there are no matches, the left table records are still returned.
  • RIGHT (OUTER) JOIN s the opposite of a left join: all the rows from the right table are returned, along with any matches in the left table.
  • FULL (OUTER) JOIN returns any records with a match in either table.
  • UNEQUAL JOIN: Non-matching records from both tables are results.

5. Alias

The Alias command is used to temporarily rename a table. It is a nickname which exists inside the individual transaction you are running.

Here's how you use it:

        SELECT A.age FROM people as A;
    
SQL interface

You can use any valid name you like, but it’s always best to use letters of the alphabet. Before each column name, the alias is prefixed.

This alias is assigned to the table immediately after it is declared. It's the same as doing this:

        SELECT people.age FROM people;
    

Rather than typing a long table name, you can type a simple and easy to remember letter.

If you're selecting from more than one table, it's easy to get confused about which columns belong to which table. If both tables happen to have columns with the same name, your database query will fail to run without explicitly referencing the table name(s) or alias.

Here's an example with two tables:

        SELECT staff.age, staff.name, customers.age, customers.name FROM staff, customers;

Here's the same query with aliases:

        SELECT A.age, A.name, B.age, B.name FROM staff A, customers B;
    

The staff table is given the alias of A, and the customer table is given the alias of B. Aliasing tables helps make your code easier to understand and reduces the amount of typing you must do.

You can also rename a column with an alias using the AS command:

        SELECT age AS person_age FROM people;
    
SQL interface

When this query is run, the column will now be called person_age instead of age.

6. Union

Union is a great command, as it allows you to append rows to each other. Unlike joins which append matching columns, union can append unrelated rows provided they have the same number and name of columns.

        SELECT age, name FROM customer
UNION
SELECT age, name FROM staff;
SQL interface

You can think of union as a way of combining the results of two queries. A union will only return results where there is a unique row between the two queries.

You can use the UNION ALL syntax to return all the data, regardless of duplicates:

        SELECT age, name FROM customer
UNION ALL
SELECT age, name FROM staff;
SQL interface

Notice how the order of the rows change? Union operates in the most efficient way, so the returned data can vary in order.

A possible use case for union is a subtotal: you can union a query of the sum total onto a query of the individual totals for a particular scenario.

7. Insert

At times, you want to add new data into an existing database/table. This is where the insert command in SQL comes in.

The VALUES syntax is used to provide the values to insert.

        INSERT INTO people(name, age) 
VALUES('Joe', 102);

You must specify the table name (people), and the columns you wish to use (name and age). The VALUES syntax is used to provide the values to insert. These must be in the same order as the columns which were previously specified.

You can’t specify a where clause for inserts, and you need to ensure you follow any necessary table constraints that are present.

8. Update

After inserting some data, it's only natural to need to change specific rows. Here's the update command syntax:

        UPDATE people SET name = 'Joe', age = 101;
    

You have to specify the table you wish to change, and then use the SET syntax to specify the columns and their new values.

To be more specific, you can use WHERE clauses just like when doing a select statement:

        UPDATE people SET name = 'Joe', age = 101 WHERE name = 'James';
    

You can even specify multiple conditions using AND/OR:

        UPDATE people SET name = 'Joe', age = 101 WHERE (name = 'James' AND age = 100) OR name = 'Ryan';
    

Notice how the brackets are used to constrain the conditions.

9. Upsert

Upsert is a strange sounding word, but it is an incredibly useful command. Say you have a constraint on your table, and you've specified that you only ever want records with unique names; you don't want to store two rows with the same name, for example.

If you tried to insert multiple values of Joe, your database engine would throw an error and refuse to do it (quite rightly).

An UPSERT allows you to update a record if it already exists. This is incredibly useful! Without this command, you will have to write a lot of logical statements to first check if a record exists, insert if it does not, otherwise retrieve the correct primary key and then update.

Unfortunately, upserts are implemented differently in different database engines. PostgreSQL has only recently gained this ability, whereas MySQL has had it for quite a while. Here's the MySQL syntax for reference:

        INSERT INTO people(name, age)
VALUES('Joe', 101)
ON DUPLICATE KEY UPDATE age = 101;

Notice how this is essentially an update and an insert statement, which can be summed up as update if insert failed.

10. Delete

Delete is used to remove records entirely; it can be quite damaging if used in the wrong manner.

The basic syntax is very easy to use:

        DELETE FROM people;
    

Like most of the other commands, this will delete everything. You need to use a where to restrict it to a slightly saner number of rows, ideally one:

        DELETE FROM people WHERE name = 'Joe';
    

If you're developing a system, it's often wise to implement a soft delete. You never actually run the delete command; rather you create a deleted column, and then check that column in your selects. This can avoid a lot of potential embarrassment, if you can quickly and easily retrieve supposedly deleted records. This is no substitute for proper backups, however.

11. Create Table

The create table command is used to create tables. It's another simple and basic query process:

        CREATE TABLE people (
  name TEXT,
  age, INTEGER,
  PRIMARY KEY(name)
);
SQL interface

Notice how the column names and constraints are inside brackets, and the columns are given an appropriate datatype.

Related: How to Create a Table in SQL

A primary key is specified, as is required in any good database design.

12. Alter Table

The alter command in SQL is used to modify the structure of a table. This is slightly limited, as your database will not let you alter a table if the existing data would cause a conflict—changing a string to an integer, for example. In those instances, fix the data first, then modify the table. Here's an example:

        ALTER TABLE people ADD height integer;
    

This example adds a column called height of type integer to the people table. There's not really a limit on what you can alter.

13. Drop Table

The final command is drop table. Think of this as delete, but rather than deleting a single record, it removes every single record along with the table. Here's how you can use it:

        DROP TABLE people;
    

It's quite a drastic command, and there's no reason it needs to be programmed into your system. It should only be performed manually in the vast majority of cases, as it can be quite destructive.

SQL Commands for Every Programmer Out There

SQL is a very common, yet powerful tool, which can help extract, transform and load data from/into databases. The very essence of data querying rest on SQL. To master the language, you need to know how to work your way through some commands for best use cases.