in this lesson we will talk about outer left join in sql
as we mentioned earlier the join query gets result from two or more tables usually the table on the
left side is called left table and the other table is called right table when we run the
left join on two tables we get all the rows from the left side or from the left table and only the
rows from the right table is returned where the condition is met for example we have an employee
table we have a primary key on employee id and we have a foreign key on city id through the city id
this employee table is linked to city table as we mentioned the city id is a primary key and
it has been used as a foreign key inside employee table so when we run the left join query on these
two tables the left query is returning all the rows from the table employee and only those rows
from city table where the condition is met so the result will be 11 employees without
two city because these two cities has not been assigned to these two employees so let's go to
sql studio and see how to write left join query and how to see the result in practice here we have
album database selected and we have city and employee table now if i want to write that left
query syntax i will start with select then i have to write the column names from the table which
i would like to see it in my query result as i mentioned in my previous video it is good practice
to write the table name before the column name in this case i'll write employee dot first name
then employee as a table name dot last name then the I would like to see city name from
table city so I will write city as a table name then city name
then from after from you have to write table name the table name which comes after from will
considered as the left table, in this case, I'll write employee so the employee will be our left
table then I'll write left join then I have to write the second table name which is a city on now
I have to specify the column name which these two tables are linked together so I'll write employee
city id equals city dot city id so if I run this query it will return all the records inside the
employee table and only those records in the city table where the condition is met as you see we
have all the rows from the table employee and only those records from city table where the condition
is met as you see two cities is empty because this because it is not assigned to these two
employees, of course, we can use order by and where clause with left join as well for example
if I want to sort my result by name, for example, I will write order by first name descending
run it again as you see the result has been sorted out descending by the first name
also you can use the where clause as well you can filter your result by city name or
other columns based on your requirement for example I'll write where city name
like you follow so if I run this query it will filter my result by city name and only returns