Database Techniques

Lecture 5
SQL: JOIN - DML

nazila.hasanzade@hkr.se
 Recap: SELECT-Syntax
SUM( column )
SELECT value_to_display AVG( column )

FROM table_name Count( column )

WHERE expression MAX( column )

GROUP BY how_to_group MIN( column )

UPPER( column )
HAVING expression
LOWER( column)
ORDER BY how_to_sort CONCAT( col1, col2 )
LIMIT row_count;

2
 JOIN

3
 Purpose of JOIN
JOIN statements allow us to select related information from more
than one relation at the same time.

Example: We need to select and show a patients'

name and what date he visited the doctor.

4
 Example 1

This is how it would look in SQL:

SELECT patient_name, date

FROM patients, visits
WHERE patients.patient_id = visits.patient_id;

The result of running this query will be:

5
 Explaining Example 1
First thing we need to do is to look at the tables again:

1. Note that we have patient_id in both tables

2. Note that we have three rows in each table

visits
visit_id patient_id date fee
10 760606-3140 2011-01-12 300
11 540912-1818 2011-05-18 300
12 831211-8923 2011-06-24 300
patients
patient_id patient_name address
760606-3140 John Doe Elm Street 6
540912-1818 Eve Smith Downing Street 32
831211-8923 Ron Lind Main Road 44

6
 Cartesian product
Or the “Ops I forgot the WHERE clause”.

• Try to avoid as it is most likely not what you are looking for,
and besides, it can be really slow. 

• Cartesian product combines each and every row from table

one with all the rows from table two in all possible ways.

SELECT patient_name, date

FROM patients, visits;

7
 Explaining Example 1

Patients table Visits table

Step 1 Step 2
Combine each row in the the tuple(s) where the
Find th
left table with every row patient_id is the same
in the right table

8
 Explaining Example 1
Step 3
Look at the rows we got when we selected the ones with the same
patient_id in Step 2. Now select the attributes specified in the
initial SQL statement.

SELECT patient_name, date

FROM patients, visits
WHERE patients.patient_id = visits.patient_id;

9
 Another Example

Let us look at another example where we use the World database.

Our task: Show the name of all countries and what languages
that is spoken in each country.

10
 Explaining Example 2
Constructing the SQL statement we need can be done in steps.

SELECT name, language Step 1

Decide what columns the
final result shall contain.

FROM country, countrylanguage

Step 2
Figure out what tables we
WHERE Code = CountryCode; need to use.

Step 3
What columns link the two tables
together? The columns shall have
the same domain. (PK / FK)

11
 Explaining Example 2
Here is the SQL statement once more:

SELECT name, language

FROM country, countrylanguage
WHERE Code = CountryCode;

Result:
As can be seen in the picture to the right, we will
get two columns name and language. The data will
be one row for each country name / language combination
for all languages spoken in each country.

12
 Referring to tables

Now let us look at two ways of making this code runnable

and also easier to understand and/or read.

13
 Referring to tables

The first way: By referring to attributes in the form

<table name>.<attribute name>
When name of
the attributes
SELECT country.Name, city.Name are the same in
both tables!
FROM country, city
WHERE country.Code = city.CountryCode;

14
 Referring to tables
The second way:

SELECT c.Name, ci.Name

FROM country c, city ci
WHERE c.Code = ci.CountryCode;

Another example:
SELECT c.Name, cl.Language
FROM country c, countrylanguage cl
WHERE c.Code = cl.CountryCode;

15
 Alias
The third way: By naming tables and then using the names as a
prefix to all attributes (also called Alias)

(Read more about Alias here: http://www.w3schools.com/sql/sql_alias.asp)

SELECT column_name
AS alias_name
FROM table_name;
-------------------------------------------
SELECT column_name(s)
FROM table_name
AS alias_name;

16
 Alias - Example
• Renaming columns with AS
SELECT city.name AS city_name, country.name AS country_name
FROM city, country
WHERE city.CountryCode = country.Code;

Without AS Using AS

17
 Types of Join
• Cross join
• Inner join (Basic Join)
• Self join
• Left join (Outer Join)
• Right join (Outer Join)

18
 Cross Join (= cartesian product)
• Give an example of cross join.
• Bring for- and against arguments for using
cross-join.

19
 Cross Join (= cartesian product)
Or the “Ops I forgot the WHERE clause”.

• Try to avoid as it is most likely not what you are looking for,
and besides, it can be really slow. 

• Combines each and every row from table one with all the rows
from table two in all possible ways.

Example of CROSS JOIN and the corresponding variant without using the key
words CROSS JOIN.

SELECT name, language

FROM country SELECT name, language
CROSS JOIN countrylanguage; FROM country, countrylanguage;

Table 1 Table 2

20
 Basic join (inner join)
• Used to get information from two or more tables at the same time.
• The information shall match the conditions

Example : Show the name of all cities and also show the country
name after each city.

SELECT city.Name, country.Name

Alternative 1 FROM city, country
WHERE city.CountryCode = country.Code;

Table 1 Table 2

SELECT city.Name, country.Name

FROM city
Alternative 2 JOIN country
ON city.CountryCode = country.Code;

21
 Basic join (inner join)
Example : Show the name of all cities that has population more than
one miljon and also their corresponding country.

SELECT city.Name, country.Name

Alternative 1 FROM city, country
WHERE city.CountryCode = country.Code
AND city.population > 1000000;

SELECT city.Name, country.Name

FROM city
Alternative 2 JOIN country
ON city.CountryCode = country.Code
WHERE city.population > 1000000 ;

22
 Left/Right join (Outer Joins)

Left Join Table 1 Table 2

• Returns all rows from left table, even if there is no match in the
right table, also the matched records from the right table.

SELECT country.Name
FROM country LEFT JOIN city
ON country.Code = city.CountryCode

Where there is no match in right table (city), the result is NULL.

See also: http://www.w3schools.com/sql/sql_join_left.asp

23
 Left/Right join (Outer Joins)

Left Join Table 1 Table 2

• Can be used when we are looking for rows that are not linked
in another table.

Example: Let’s show all countries that have no cities.

SELECT country.Name, city.Name
FROM country
LEFT JOIN
city ON country.Code = city.CountryCode
WHERE city.name is NULL;

24
 Left/Right Join (Outer Join)

• Right Join Table 1 Table 2

• Works in the same way as Left Join, but it takes all the rows from the
right table, even if there is no match in the left table, together with the
matched records from the left table.

See also:
http://www.w3schools.com/sql/sql_join_right.asp

25
 Self join
Example 1

Question: Name of the employees (firstname,lastname) and who they

are reporting to.

26
 Self join
Example 2: Find all cities located in the same country as Kabul.

SELECT c1.name
FROM city AS c1, city AS c2
Alternative 1
WHERE c1.CountryCode = c2.CountryCode
AND c2.name = ‘Kabul’;

SELECT name FROM city WHERE

Alternative 2 CountryCode IN (SELECT
CountryCode from city where
city.name ='kabul');

27
 Subqueries
• A subquery (also called nested query) is a
SELECT statement that is placed within
parentheses inside another SQL statement.

• Many names: query in query, select in select,

nested selects/queries…

• Uses the result of a query as input for another

query.

28
 Subqueries

Example: Let us select all cities located in Europe.

 Bad way: SELECT Name FROM city WHERE CountryCode =

'SWE' OR CountryCode = 'POL'… and so on….

 Still bad way: SELECT * FROM city WHERE CountryCode IN

('SWE', 'POL');

29
 Subqueries
☺ Good way:

SELECT name
FROM city
WHERE CountryCode IN (SELECT code
FROM country
WHERE Continent = 'Europe');

• When SQL locates the blue IN, it first executes the purple statement
and saves the result as a temporary table to be used when we
execute the black SELECT statement above.

30