join

Joining Many Tables (Introduction to Oracle SQL)



Sharing buttons:

joining many tables we've looked at many

different joint types in this section

inner join and left join are probably

the most common we've only looked at two

tables at once what if we want to select

Darla for more than two tables

we can select from more than two tables

in SQL that's the big advantage of

relational databases and having data in

separate tables it's quite flexible in

what we can show do you remember this

diagram from earlier in the course

it shows the tables in our database some

of the columns might be familiar to you

now have a look at the bottom of the

diagram we have the customer customer

order and product tables what if we want

to see data from all three of those

tables we can do this in SQL to join on

multiple tables we just add another

occurrence of the join keyword then the

next table as you can see here we have

select from table 1 then we join table 2

on some columns then we add join and

then we add the third table we then add

another on Clause where we join table 2

to table 3 the join is added here so

table 3 can be included in the query

it's joined to the tables that have been

selected so far the on keyword specifies

how it's joined so we know whether to

join the data to table 1 or table 2 we

can keep going to add more tables if we

need to we can also specify different

join types for each join a combination

of inner left-right full and so on let's

see some examples of joining on multiple

tables so we're going to try to join

three tables together now this is

because we want to see customer first

name last name there stays the order

date the products they ordered and their

price these are stored in three separate

tables so let's select the customer data

first customer ID first name last name

address state from customer let's give

it an alias and run the query you can

see our customer data here now let's

select from custom order

now this is our customer order table

finally or select from a product table

let's do a select star just so we can

see the columns so those are our tables

let's now write a query to get data from

all three in the one query we'll adjust

our first customer table query here now

we're going to join it to the customer

order table we're using a normal join or

an inner join because we only want to

see customers that have orders we're

going to give an alias here to make it

easier for us on see customer ID equals

co dot customer ID as well now let's run

this query

it says column ambiguously defined this

is because we have the customer ID

column which exists in two tables so

let's put in an alias in front of it and

run our query you can see it show you

some customer data here and it looks

like it's duplicated this is because we

haven't put in the order information so

we've put in our order ID and order date

here let's run the query again you can

see that all the order IDs are showing

here with their customers now let's join

to the products table we do this by

writing another join keyword and then

the product table let's give the product

table an alias of P now how do we join

to the product table the product table

joins to customer order based on the

product ID column so we're joining on

CEO dot product ID equals P dot product

ID now let's add a couple of columns

from the product table P dot product

name and P dot price if we run this

query we can see data from three tables

that all line up with each other and all

in the one query we can see Fred

Montgomery here has had four orders in

each order these are the products that

he ordered and these were the dates you

can scroll through and see the rest of

the customers orders as well now because

we have the data all in one query we can

order it in any way that we like

we can order by custom ID or we can

order it by order ID

you can see that the data is now sorted

by water ID we can order it by any

combination of columns just as we've

learned to do in earlier lessons so our

example here used inner joins for all

tables what if we wanted to see all

customers and show order and product

details if they have any in this case we

need to select from customer and then

left join to customer order and then the

inner join to product can remain the

same let's run this query now let's

order by customer ID it's still not

showing any data where customers don't

have any orders let's left join to the

product table and run the query now we

can see the customer ID 1 and customer

ID for both have rows here but they have

null values for orders and products now

let's add a fourth table we know that

the product table has a department ID

column so let's join to the department

table

we're going to join on P Department ID

which is the department ID column in a

product table

- the department ID column in the

department table we're also going to

display the department name now let's

run the query you can see that the

products all have the department listed

next to them as well as the data from

all of the other tables so that's how

you can join to multiple tables joins

are a really powerful concept and they

are the main advantage to using

databases and the SQL language now if

you're unsure about anything in this

section please let me know

understanding joins will really help

your SQL skills