join

SQL: Understanding the JOIN clause in the SELECT statement



Sharing buttons:

hello there and welcome my name is

Michael fudge and this is yet another

SQL screencast

this screencast will talk about the join

clause in the SQL select statement now

as you already know the SQL select

statement retrieves data from a table so

I can type something like this to list

out the basketball players that I might

have in my league let's say so this is

my sample basketball league and I can

also also list out the teams in my

league so let me just expand this out a

bit and you'll see that I have five

players three teams this player fudge

does is not on a team and this

particular team the stinkers has no

players on it because you either play

for Team 100 or team 101 and this is

team 102 so this small little example

will hopefully demonstrate the different

ways that tables can be joined together

and maybe get you a better to better

understand the rationale behind each of

the different join techniques so to

start out let's begin with the most

natural of joins the inner join for

example you might want to know which

players play for which team so you can

say select from B be player join B be

team

on player team ID equals team Heidi

now before I run this let's just talk a

whisker about this you're saying give me

all the columns from this table join it

with this table where this on is like in

effect saying under the conditions where

the players team ID from the player

table matches up with the team ID from

the team table so what happens when

players and teams don't match up they

just don't get shown so for example I

have Jordan on team 100 bowls O'Neal on

team 101 Lakers Pippen on team 100 Bulls

Ryan on team 101 Lakers and if we look

back at the original tables there's four

rows in the query output the original

table has five rows in it so we aren't

missing fudge who's a player and we are

missing stinkers the team and the reason

we're missing those is because of the

join condition that says show me

everything from these two tables where

this condition matches by the way this

is called SQL 92 syntax in the in the

olden days we write this equivalent SQL

statement like this under SQL 89 syntax

maybe player comma B B team where player

team ID equals team ID we write the

equivalent SQL like this in SQL 89

syntax and you get the same output it's

interesting to note that the the reason

that this is a preferred and better

method is you're you're doing the logic

at the table join level which leaves you

the ability to add the where Clause into

the statement if you want to further

sort this and say well I only want to

show where they're armed player

name is like Jordan right I can run this

statement here execute it and I'm going

to do this right there we go I'll get

Jordan and if I were going to do the

same thing down here the where Clause

becomes a little more complicated is

that where the team ID and the player

team ID match and player name like

Jordan so you can see that the the where

clause gets a little mocked up on from

the fact that we have some join logic in

here and we also have just a regular

filter logic occurring so there's a

clear separation in the above syntax of

the join logic which is here from the

filter logic which is here bottom

example brings up another interesting

point and that that is what if we were

to join these things what if we were to

join these things just like so well we

get if you do that you get what's called

a cross join and a cross join is really

just a complete enumeration of all the

rows in one table with all the rows in

the other table for example if you look

at the output there's 15 rows here and

what we're seeing is an enumeration of

every combination of player with team

not very useful in this particular

circumstance but there are there are

times where there there could be valid

that valid applications of this type of

joint logic in SQL 89 that's how you two

into 92 you would say from B be player

cross join B be team like that and it

gives you the same results okay now

let's talk a little bit about outer join

logic and why you might need outer join

logic now outer joins allow you to do

things like see all the table all the

players even the ones that aren't on

Timur see all the teams including the

teams without players so let me kind of

show you how this works first I can say

select everything from player left join

BB team on player team ID equals team

Heidi the left join says include all the

rules from the left table even the ones

that that don't match up with this

because of null so if I execute this

oops

execute this I get all the players those

matched up with the teams and then me

the guy without a team it's just null in

the team section so again what left

joint is saying is on the left table

show me all the all the real rose out of

the left table okay even the ones where

they don't match up because they're not

if I were to change this to a right join

now what happens is it shows all the

teams so now what I'll see is stinkers

and I do you see all the players that

come that have matched up with teams are

shown and only the team with no player

shows up over here that's a right joint

the left and the right don't matter as

much as where the table is so for

example if I were to flip these and put

on BB team here and BB player here VB

team right join BB player is the same as

BB player left join BB team it's the

same same thing so now you'll see I see

fudge back in here it's just the column

orders are a little different but the

point is that left or right it doesn't

matter what matters is if you say left

join it's the left table that you're

going to see all the rows from if you

say right join is the right table you're

going to see all the rows from but you

really you could use left or right join

so now comes the last case where you

might want to see not only the all the

players that don't have teams but all

the teams that don't have players and

that would be a full join when you do a

full join

what you'll get is you know players that

obviously match up with teams and then

you'll also get teams with no players

and players with no teams so this

additional join logic really helps you

determine very specific circumstances

like if you're trying to find specific

players without teams or teams without

players and include that in the output

of players with teams well I hope that

clears the muddy waters of how the join

Clause works especially with regards to

outer joins and how they include data

from tables that are null across the

joint thank you for joining for the

screencast and hope to see again