SQL connection

How to perform a simple join between two database tables

Connection is a very powerful tool. Remember the relational algebra in the database introduction module?

Connection isApplied relational algebra.

Suppose you have 2 tables,peoplewithcars:

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

Let's 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, we want to associate these two tables because the police stopped Roger from driving, he looks very young, and wants to know his age from their database.

Roger is my dog, but let us assume that the dog can drive.

We can create aJoinUse the following syntax:

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

We will get the following results:

 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 cars table.


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:

Online technical encyclopedia!