All language subtitles for [SubtitleTools.com] Joins - Learning Oracle 12c [Video]

In this lesson, we're
going to take a look

at the concept of a join.

The relational model
for relational databases

says that table data can be
split into different tables.

So we can have one table
with employee information

and one table with
department information.

So using a SELECT
statement, we can get data

from either of those tables.

But how do we make a
connection between the two?

How do we leverage this
concept of a relationship

between relational tables?

And that's what the
join is all about,

and there's a lot of different
ways that we can do joins.

So let's jump in
here by connecting

to our ORCL database
and our Scott user,

and let's expand the table list.

All right.

So we have a table
with employee data

and a table with
department data.

Let's say rather than querying
each of these individually,

we need to find a way
to take some information

from the employee table
and some information

from the department table
and display them together.

Let's click the plus
by each one of these.

The way that we're
going to leverage a join

is by joining on
a common column.

So let's see what we have
between these two tables.

Do they have a relationship?

If they have a relationship,
they'll have a common column.

So notice that the Department
table has a DEPTNO column,

and the EMP table has a
DEPTNO column, as well.

So we could say they
have a common column.

If they do, which in
this case they do,

we can use that
common column to form

a relationship between employee
data and department data.

And we do this with
a SELECT statement.

Before we leverage
the use of the join,

let's take a look
at what happens when

we don't leverage the join.

So here we're going to say
select star from dept comma

emp.

All right.

When we normally use
a SELECT statement,

we will say select star or
column list from a table.

But here we have two tables.

So we're saying I want all
the data from the DEPT table

and the EMP table.

Let's click Execute.

What do we have here?

Well, the EMP table has
about 12 rows in it.

The DEPT table has, I
believe, five or six.

And yet, look at all
this that's come back.

How has this helped us?

This is what's known
as a Cartesian join,

a Cartesian product,
or a cross product.

It's referred to as all three
in different situations.

And what has happened
here is that our command

is taking every row
from the EMP table

and joining it to every
row in the DEPT table.

So there's DNAME, LOC.

These are from the DEPT table.

And it's joining it to
every row in the EMP table.

And so what you get is far more
data, and really useless data

in most cases.

So a Cartesian join,
or a Cartesian product,

is usually a mistake, and it
can be, with large tables, one

of the biggest performance
problems, or performance

mistakes, in a
relational database

because it brings back orders
of magnitude, more data,

than you actually want.

So we need to do a join, and
we need to do it the right way.

So let's start over.

And let's pick the
columns we want.

So I want to know
the employee name.

I want to know
their job, manager.

And from the DEPT table,
we'll put deptno and dname

from dept table comma emp table.

Now, this would give
us just fewer columns

and the Cartesian product.

So what we need is some sort
of way to do the join actually

syntactically in the code.

Now, there's two
ways to do this.

There's what's kind of referred
to as a deprecated way and then

the ANSI standard way.

And it's interesting because
the deprecated way is actually

far more common than
the ANSI standard way.

The deprecated method would
add a WHERE clause, some kind

of limiting condition.

And it would basically be this--

where deptno equals deptno.

So right?

These are the columns
that are common, right?

So we want to set those equal.

But this isn't going to work
for us because it's not going

to know which column is which.

And so we need to set up a
way here to identify these.

And the way we can do this is
through table dot notation.

So we preface the
column with the name

of its corresponding table.

So we have a DEPTNO column
in the DEPT table and the EMP

table.

So we're going to
set those equal.

Now, what we also
might want to do

is preface these columns up
here with the DEPTNO table dot

notation for each column.

For instance, this DEPTNO
column is not identified, right?

So which one is it?

So we'll say this is EMP DEPTNO.

Now let's execute.

So this is much more
what we might expect.

There are 14 rows
in the EMP table.

And notice we have
14 total rows here.

And here is our EMP
information, and here

is our DEPT information.

So it shows Clark as a
manager and his department

and his department name, so
on and so forth through here.

So we've used table dot notation
in order to specify this.

We could also do
it a different way.

And actually, that
way is pretty common.

And that's using alias notation.

So what I've done
here is I've created

an alias for the DEPT
table called d and an alias

for the EMP table called e.

And so now, instead of putting
the full table name in front,

I'm just going to
put this alias.

And we need to do
it here, as well.

So now, the e is going to
refer to the EMP table,

and the d is going to
refer to the DEPT table.

And notice that it brings
back the same data.

What about the ANSI
standard notation?

Well, let's take a look at that.

There's a couple
of different ways

we can use the ANSI
standard notation.

Take this out.

We're going to get back
to just the columns

that we actually want.

We could use what's called
the natural join syntax.

We could say from
emp natural join

dept. Let's click Execute here.

Notice that it brought
back the same information.

But we didn't tell it how
to join the two tables.

We didn't say, set
this column value

equal to this column value
in a different table.

And we didn't even clarify which
column we were referring to

in this case.

Well, the natural join syntax
is a little bit smarter,

if you will.

And so it looks at the
two tables and says,

there are two columns, one in
one table and one in another,

and those columns
are named the same,

and it relates those two
together sort of naturally.

That's why it's
called natural join.

It makes a natural
join between the two

and then presents the data.

So that's the
natural join syntax.

Another way we can do this
using the ANSI standard notation

is join using.

So let's say from
emp join dept using,

and in parentheses we'll
specify the column.

So what we've done here, using
the ANSI standard notation,

is we're going to use
clauses join and using.

So we're saying join the EMP
table to the DEPT table using

this column, DEPTNO.

And notice the data
that it brings back

is exactly what we've seen
in each one of these joins.