1
00:00:05,990 --> 00:00:08,595
Hello and welcome.

2
00:00:08,595 --> 00:00:10,020
In this video,

3
00:00:10,020 --> 00:00:12,119
you will learn how to write queries

4
00:00:12,119 --> 00:00:15,120
that access more than one table.

5
00:00:15,120 --> 00:00:17,625
There are several ways to access

6
00:00:17,625 --> 00:00:20,370
multiple tables in the same query.

7
00:00:20,370 --> 00:00:24,705
Namely, using Sub-queries, Implicit JOIN,

8
00:00:24,705 --> 00:00:30,015
and JOIN operators, such as INNER JOIN and OUTER JOIN.

9
00:00:30,015 --> 00:00:33,830
In this video, we'll examine the first two options.

10
00:00:33,830 --> 00:00:35,480
The third option is covered in

11
00:00:35,480 --> 00:00:38,090
more detail in other videos.

12
00:00:38,090 --> 00:00:41,000
Let's consider the employees and

13
00:00:41,000 --> 00:00:43,895
departments tables from a previous video.

14
00:00:43,895 --> 00:00:46,040
The employees table contains

15
00:00:46,040 --> 00:00:47,809
several columns for categories,

16
00:00:47,809 --> 00:00:49,415
such as employee ID,

17
00:00:49,415 --> 00:00:51,080
first name, last name,

18
00:00:51,080 --> 00:00:53,105
and salary to name a few.

19
00:00:53,105 --> 00:00:56,750
The Departments table contains a department ID,

20
00:00:56,750 --> 00:01:01,175
department name, Manager ID, and location ID.

21
00:01:01,175 --> 00:01:04,985
Some sample data from these tables is shown here.

22
00:01:04,985 --> 00:01:06,920
We will utilize these tables

23
00:01:06,920 --> 00:01:09,335
for the examples in this video.

24
00:01:09,335 --> 00:01:11,525
In a previous video,

25
00:01:11,525 --> 00:01:14,015
we learned how to use sub-queries.

26
00:01:14,015 --> 00:01:18,410
Now, let's use sub-queries to work with multiple tables.

27
00:01:18,410 --> 00:01:22,130
If we want to retrieve only the employee records from

28
00:01:22,130 --> 00:01:23,780
the employees table for which

29
00:01:23,780 --> 00:01:27,305
a department ID exists in the departments table,

30
00:01:27,305 --> 00:01:30,340
we can use a sub-query as follows.

31
00:01:30,340 --> 00:01:32,700
Select star from employees,

32
00:01:32,700 --> 00:01:35,345
where department_ID IN,

33
00:01:35,345 --> 00:01:39,950
select department_ID_department from departments.

34
00:01:39,950 --> 00:01:42,590
Here the outer query accesses

35
00:01:42,590 --> 00:01:45,080
the employees table and the sub-query

36
00:01:45,080 --> 00:01:47,180
on the departments table is used for

37
00:01:47,180 --> 00:01:50,845
filtering the result set of the outer query.

38
00:01:50,845 --> 00:01:53,480
Let's say we want to retrieve

39
00:01:53,480 --> 00:01:57,140
only the list of employees from a specific location.

40
00:01:57,140 --> 00:01:59,885
We do not have any location information

41
00:01:59,885 --> 00:02:01,550
in the employees table,

42
00:02:01,550 --> 00:02:03,320
but the departments table has

43
00:02:03,320 --> 00:02:06,160
a column called location ID.

44
00:02:06,160 --> 00:02:09,440
Therefore, we can use a sub-query from

45
00:02:09,440 --> 00:02:11,810
the Departments table as input to

46
00:02:11,810 --> 00:02:14,690
the employee table query as follows.

47
00:02:14,690 --> 00:02:19,840
Select star from employees, where department_ID IN,

48
00:02:19,840 --> 00:02:23,914
select department_ID_department from departments,

49
00:02:23,914 --> 00:02:29,645
where location ID equals L0002.

50
00:02:29,645 --> 00:02:33,200
Now, let's retrieve the department ID and

51
00:02:33,200 --> 00:02:37,955
department name for employees who earn more than $70,000.

52
00:02:37,955 --> 00:02:41,030
To do so, we will need a sub-query on

53
00:02:41,030 --> 00:02:44,720
the employees table to satisfy the salary criteria,

54
00:02:44,720 --> 00:02:48,350
and then feed it as input to an outer query on

55
00:02:48,350 --> 00:02:50,300
the departments table in order to

56
00:02:50,300 --> 00:02:53,000
get the matching department info.

57
00:02:53,000 --> 00:02:57,870
Select department_ID_department department_name

58
00:02:57,870 --> 00:03:03,690
from departments, where department_ID_department IN, select

59
00:03:03,690 --> 00:03:06,290
department_ID from employees where

60
00:03:06,290 --> 00:03:09,530
salary is greater than 70,000.

61
00:03:09,530 --> 00:03:12,890
We can also access multiple tables by

62
00:03:12,890 --> 00:03:16,505
specifying them in the FROM clause of the query.

63
00:03:16,505 --> 00:03:19,130
Consider the example, select

64
00:03:19,130 --> 00:03:22,510
star from employees, departments.

65
00:03:22,510 --> 00:03:27,140
Here we specify two tables in the FROM clause.

66
00:03:27,140 --> 00:03:30,080
This results in a table join,

67
00:03:30,080 --> 00:03:34,795
but note we are not explicitly using the join operator.

68
00:03:34,795 --> 00:03:38,410
The resulting join in this example is called a

69
00:03:38,410 --> 00:03:41,710
full join or Cartesian join,

70
00:03:41,710 --> 00:03:44,065
because every row in the first table

71
00:03:44,065 --> 00:03:47,350
is joined with every row in the second table.

72
00:03:47,350 --> 00:03:50,019
If you examine the results set,

73
00:03:50,019 --> 00:03:53,830
you will see more rows than in both tables individually.

74
00:03:53,830 --> 00:03:58,675
We can use additional operands to limit the result set.

75
00:03:58,675 --> 00:04:01,660
Let's look at an example where we limit the result

76
00:04:01,660 --> 00:04:05,560
set to only rows with matching department IDs.

77
00:04:05,560 --> 00:04:11,375
Select star from employees, departments, where employees

78
00:04:11,375 --> 00:04:15,080
department_ID equals departments,

79
00:04:15,080 --> 00:04:18,140
department_ID_department.

80
00:04:18,140 --> 00:04:20,865
Notice that in the WHERE clause,

81
00:04:20,865 --> 00:04:22,670
we prefix the name of

82
00:04:22,670 --> 00:04:25,310
the column with the name of the table.

83
00:04:25,310 --> 00:04:28,445
This is to fully qualify the column name,

84
00:04:28,445 --> 00:04:30,890
since it's possible that different tables could

85
00:04:30,890 --> 00:04:33,995
have some column names that are exactly the same.

86
00:04:33,995 --> 00:04:37,580
Since the table names can sometimes be long,

87
00:04:37,580 --> 00:04:42,065
we can use shorter aliases for table names as shown here.

88
00:04:42,065 --> 00:04:47,120
Select star from employees E, departments D,

89
00:04:47,120 --> 00:04:54,280
where E department_ID equals D department_ID_department.

90
00:04:54,280 --> 00:04:57,560
Here, we define the alias E for

91
00:04:57,560 --> 00:05:01,310
employees table and D for departments table,

92
00:05:01,310 --> 00:05:05,435
and then use these aliases in the WHERE clause.

93
00:05:05,435 --> 00:05:07,340
If we wanted to see

94
00:05:07,340 --> 00:05:09,740
the department name for each employee,

95
00:05:09,740 --> 00:05:12,200
we would enter the code as follows:

96
00:05:12,200 --> 00:05:14,045
Select

97
00:05:14,045 --> 00:05:18,260
Employee_ID, Department_Name

98
00:05:18,260 --> 00:05:22,200
from employees E, departments D,

99
00:05:22,200 --> 00:05:26,210
where E department_ID equals D

100
00:05:26,210 --> 00:05:29,050
department_ID_department.

101
00:05:29,050 --> 00:05:33,635
Similar to before, the column names in the select clause

102
00:05:33,635 --> 00:05:38,420
can also be prefixed by aliases as shown in the query.

103
00:05:38,420 --> 00:05:44,785
Select E. Employee_ID, D. Department_ID_department,

104
00:05:44,785 --> 00:05:49,020
from employees E. Departments D where

105
00:05:49,020 --> 00:05:54,300
E.Department_ID equals D. Department_ID_department.

106
00:05:54,300 --> 00:05:56,780
In this lesson, we have shown

107
00:05:56,780 --> 00:05:59,000
you how to work with multiple tables

108
00:05:59,000 --> 00:06:00,830
using sub-queries and

109
00:06:00,830 --> 00:06:04,830
implicit joins. Thanks for watching.