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.
All the examples are based on four fictional tables. The customer table contains the name and age of customers:
The heights table contains the name and height of any person:
The staff table contains the name and age of staff members, exactly the same as the customer table:
The final table people contains the name and age of people, just like the customer and staff tables:
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;
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;
Any string inside single quotes will be returned instead of matching a column name.
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
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;
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';
This query returns records where the age is greater than 90, or the name is equal to Joe.
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;
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;
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.
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);
You have to start with the LEFT JOIN syntax, which specifies that you want to join a table using a join of type left.
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;
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.
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;
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;
When this query is run, the column will now be called person_age instead of age.
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;
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;
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.
At times, you want to add new data into an existing database/table. This is where the insertcommand 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.
After inserting some data, it’s only natural to need to change specific rows. Here’s the updatecommand 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.
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.
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) );
Notice how the column names and constraints are inside brackets, and the columns are given an appropriate datatype.
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.