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

af Afrikaans
ak Akan
sq Albanian
am Amharic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranรฎ)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu

Original subtitles

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.

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.