Joins are a very powerful tool. Remember relational algebra from the database intro module?
Joins are applied relational algebra.
Suppose you have 2 tables,
CREATE TABLE people ( age INT NOT NULL, name CHAR(20) NOT NULL PRIMARY KEY ); CREATE TABLE cars ( brand CHAR(20) NOT NULL, model CHAR(20) NOT NULL, owner CHAR(20) NOT NULL PRIMARY KEY );
We add some data:
INSERT INTO people VALUES (37, 'Flavio'); INSERT INTO people VALUES (8, 'Roger'); INSERT INTO cars VALUES ('Ford', 'Fiesta', 'Flavio'); INSERT INTO cars VALUES ('Ford', 'Mustang', 'Roger');
Now say that we want to correlate the two tables, because the police stopped Roger driving, looks young, and want to know his age from their database.
Roger is my dog, but let’s suppose dogs can drive cars.
We can create a join with this syntax:
SELECT age FROM people JOIN cars ON people.name = cars.owner WHERE cars.model='Mustang';
We’ll get this result back:
age ----- 8
What is happening? We are joining the two tables cars on two specific columns:
name from the
people table, and
owner from the
Joins are a topic that can grow in complexity because there are many different kind of joins that you can use to do fancier things with multiple tables, but here is the most basic example.
More database tutorials:
- The MongoDB basics tutorial
- How MongoDB is different from a SQL database
- Information systems, data and information
- Data models
- Introduction to the ER Data Model
- The Relational Model
- Relational Algebra
- Relational Databases
- What is a Database? And a DBMS?
- Introduction to SQL
- How to install PostgreSQL on macOS
- Do you always need a database for your app?
- How to install SQLite on macOS
- Introduction to PostgreSQL
- PostgreSQL User Permissions
- How to list all users in PostgreSQL
- How to switch database using PostgreSQL
- How to list all databases using PostgreSQL
- How to list tables in the current database using PostgreSQL
- How to install MySQL on macOS
- Creating a user on MySQL
- PostgreSQL vs MySQL, a comparison
- SQLite User Permissions
- MySQL User Permissions
- SQL, creating a table
- SQL, adding data to a table
- SQL, how to use SELECT
- SQL, Handling empty cells
- SQL, Unique and Primary keys
- SQL, how to update data
- SQL, how to update a table structure
- SQL, how to delete data and tables
- SQL Joins
- SQL Views
- How to insert multiple items at once in a MongoDB collection
- How to remove all items from a MongoDB collection