SQL: An Overview

Introduction

Perhaps you’ve heard the term ‘SQL’ before or even from its common pronunciation “sequel”, and have often wondered what it is? SQL or Structured Query Language has been around since the early 1970s and was developed by IBM as a way to manipulate and extract records from a database. The syntax for this request for information (known as a sql query) is delivered from the application to the database by a relational database management system (rdbms) such as: postgres, mysql, sqlite3, etc. It is important to note that most rdbms’s have a command line interface that lets you enter these queries manually. This data is presented on a table which consists of rows and columns similar to a spread sheet. In fact combinations of various columns from different tables can be presented as a single table as what’s known as a join. In this blog we will first go over some brief foundational knowledge before jumping into the 4 main types of join tables.

The Fundamental Basics Of SQL

I will start this with a disclaimer: as auspicious as that title sounds there are far too many basics for me to cover all of them in a single blog post. We will start on how records are looked up within a database. This is through something called a key which is a unique number that increments upward as more records are stored. There are two types of keys:

• Primary Key — is a unique number for a particular record and generally has the column name of id.
• Foreign Key — is a number held within a column of one record that references another records primary key. These usually have names that consist of the table name first followed by id.

These keys along with table names and column names can be used to present different assortments of records through something called a sql query. This is the request that is passed to your rdbms in order to retrieve data from the database. The syntax for a very basic sql query might go something like:

SELECT column_name
FROM table_name;

This would select a single column specified by the SELECT keyword from the table specified from the FROM keyword. Or if you just wanted to display all of the rows and columns of a table you can make use of the wild card * to select all columns like this:

SELECT *
FROM table_name;

There are too many keywords to go over in this blog post so here is a handy cheat sheet to check out at your leisure. You can also present columns from multiple tables at once with what is known as a join clause which is a specific query type that I will hence forth refer to as a join table. There are 4 main types of these join tables which we will cover in the next section.

The Main Four Join Tables

A lot of the utility in SQL presents itself in the ability to combine and manipulate data effectively. As previously mentioned you can combine columns/rows from different tables using a special query known as a join clause. The result is something called a join table. Lets look at the four main types of joins along with some example syntax:

• LEFT JOIN — this returns all records from the first table (or left table) along with the matching records from the second table (or right table). There will be no records from the second table presented if there are not matches. Example:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

• RIGHT JOIN — this returns all records from the second table (or right table) along with the matching records from the first table (or left table). There will be no records from the first table presented if there are no matches. Example:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

• INNER JOIN — selects all records that have matching values in both tables.
  Example:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

• FULL JOIN — this returns all of the records that have values in either the first or second table.
  Example:

SELECT table1.column_name1, table2.column_name2
FROM table1
FULL OUTER JOIN table2
ON table1.column_name1=table2.column_name2;

Conclusion

SQL has been around a very long time and with such broad implementation it’s quite safe to say it’s not going anywhere. In fact this is such a shared opinion that it even makes it into some scifi (star trek: discovery) that is based quite far into the future. I hope that this has been a good resource to give you a general idea of what SQL is and some basics as to how it’s used. There of course are much more complicated sets of data manipulation you can do beyond the scope of this blog post. Feel free to reference the postgres documentation as a more thorough learning resource.