Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.