SQL Joins: How to Perform a Simple Join Between Two Database Tables

Joins are an essential tool in SQL for correlating data from different tables. By applying relational algebra, joins allow us to combine information based on specific column values. In this blog post, we will explore a simple example of performing a join between two tables.

Let’s consider two tables, people and cars, with the following structures:

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
);

To add some sample data, we can execute the following queries:

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, let’s suppose the police stopped someone named Roger who was driving a Ford Mustang. We want to find out Roger’s age by correlating the information from the two tables.

To accomplish this, we can use a join with the following syntax:

SELECT age 
FROM people 
JOIN cars ON people.name = cars.owner 
WHERE cars.model='Mustang';

By executing this query, we obtain the following result:

1
2
3

 age 
-----
 8

In this example, we performed an inner join by matching the values of the name column from the people table with the owner column from the cars table.

Although joins can become more complex when dealing with multiple tables, this basic example demonstrates the fundamental concept of joining tables in SQL.

tags: [“SQL”, “joins”, “database”, “relational algebra”, “inner join”]