Join : SELECT Tutorial using multiple database tables

The JOIN is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables. Tables in a database are often related to each other with keys.

[info] In case you are not familiar with SELECT, visit this tutorial : SQL SELECT Tutorial : Searching & Querying the database [/info]

1) Let’s suppose we have two database tables for : contacts and countries

CREATE TABLE IF NOT EXISTS mycontacts (
    mycontacts_id int auto_increment primary key,
    name varchar(45) NOT NULL ,
    lastname varchar(45) NOT NULL,
    email text ,
    age int,
    phone varchar(45) NOT NULL,
    country_id int
)

CREATE TABLE IF NOT EXISTS country (
    country_id int auto_increment primary key,
    name varchar(45) NOT NULL ,
    phonecode int NOT NULL
)

The First table countryhas like foreign key to the country table.

2) Let’s insert some countries data:

insert into country
           (name,phonecode) 
              values
           ('USA',1),
           ('Britain',44),
           ('China',86),
           ('Denmark',45),
           ('Egypt',20);

3)Let’s view our data for the country table :

4) Let’s insert some people into the mycontacts table

     INSERT INTO mycontacts
    (name,lastname,email,age,phone,country_id)
    VALUES
    ('Jerry', 'Yang','jerry@yahoo.com','41','0911',3),
    ('Larry', 'Page','larry@google.com','45','0999',2),
    ('Bill', 'Gates','bill@live.com','45','0999',1),
    ('Michael', 'Dell','michael@dell.com','39','9838',3),
    ('Pierre', 'Omidyar','michael@ebay.com','46','9838',4),
    ('Charles', 'Flint','charles@ibm.com','120','9838',2),
    ('Lee', 'Byung-chull','lee@samsung.com','120','9838',5),
    ('Steve', 'Jobs','steve@apple.com','120','9838',1),
    ('Bill', 'Hewlett','bill@hp.com','120','9838',0);

5) Let’s show People’s name, last name and their country name OR Let’s JOIN two tables together:

The above is what we call : Natural Join
[warning]Is ‘Bill’, ‘Hewlett’ Listed on the table above ?[/warning]

6) Let’s search for people whose country is USA

7) Let’s search for people and country name where the first name is Bill

Again : Where is Bill Hewlett of HP ?
[info]JOIN: Return rows when there is at least one match in both tables[/info]
There is a different Join : LEFT JOIN:
[info]LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table[/info]
The syntax for LEFT JOIN is :

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

8) Let’s search for people and country name where the first name is Bill

Again : Where is Bill Hewlett of HP ?
9) Using LEFT JOIN Let’s search for people whose country is USA