Joins and aliases
Last updated on 2023-09-19 | Edit this page
- How do I join two tables if they share a common point of information?
- How can I use aliases to improve my queries?
- Understand how to link tables together via joins.
- Understand when it is valuable to use aliases or shorthand.
JOIN clause allows us to combine columns from
one or more tables in a database by using values common to each. It
FROM clause in a SQL statement. We also need to
tell the computer which columns provide the link between the two tables
using the word
Let’s start by joining data from the
articles table with
journals table. The
ISSNs columns in both
these tables links them.
SELECT * FROM articles JOIN journals ON articles.ISSNs = journals.ISSNs;
ON is similar to
WHERE, it filters things
out according to a test condition. We use the
format to tell the SQL manager what column in which table we are
We can represent
a LEFT join using the following
Alternatively, we can use the word
USING, as a
short-hand. In this case we are telling DB Browser that we want to
journals and that the
common column is
SELECT * FROM articles JOIN journals USING (ISSNs);
This figure shows the relations between the tables and helps to
visualise joining or linking the tables in the database: We will cover
relational database design in the
next episode. In addition to visual above, SQL
Join Types Explained Visually provides visual/animated examples
to help convey to learners what is happening in SQL
When joining tables, you can specify the columns you want by using
table.colname instead of selecting all the columns using
*. For example:
SELECT articles.ISSNs, journals.Journal_Title, articles.Title, articles.First_Author, articles.Month, articles.Year FROM articles JOIN journals ON articles.ISSNs = journals.ISSNs;
Joins can be combined with sorting, filtering, and aggregation. So, if we wanted the average number of authors for articles on each journal, we can use the following query:
SELECT articles.ISSNs, journals.Journal_Title, ROUND(AVG(articles.Author_Count), 2) FROM articles JOIN journals ON articles.ISSNs = journals.ISSNs GROUP BY articles.ISSNs;
ROUND function allows us to round the
Author_Count number returned by the
function by 2 decimal places.
SELECT journals.Journal_Title, count(*), avg(articles.Citation_Count) FROM articles JOIN journals ON articles.ISSNs = journals.ISSNs GROUP BY articles.ISSNs;
You can also join multiple tables. For example:
SELECT articles.Title, articles.First_Author, journals.Journal_Title, publishers.Publisher FROM articles JOIN journals ON articles.ISSNs = journals.ISSNs JOIN publishers ON publishers.id = journals.PublisherId;
SELECT journals.Journal_Title, publishers.Publisher, COUNT(*) FROM articles JOIN journals ON articles.ISSNs = journals.ISSNs JOIN publishers ON publishers.id = journals.PublisherId GROUP BY Journal_Title ORDER BY COUNT(*) DESC;
There are different types of joins which you can learn more about at SQL Joins Explained.
As queries get more complex, names can get long and unwieldy. To help make things clearer we can use aliases to assign new names to items in the query.
We can alias both table names:
SELECT ar.Title, ar.First_Author, jo.Journal_Title FROM articles AS ar JOIN journals AS jo ON ar.ISSNs = jo.ISSNs;
And column names:
SELECT ar.title AS title, ar.first_author AS author, jo.journal_title AS journal FROM articles AS ar JOIN journals AS jo ON ar.issns = jo.issns;
AS isn’t technically required, so you could do:
SELECT a.Title t FROM articles a;
AS is much clearer so it is good style to