_edited_edited.png)
In the first part of the blog, we covered DDL, DML, TCL, DCL, and DQL commands, in the second part of the blog we covered aggregated functions, and in the third part of the blog, we covered data filtering functions.
In this blog post, we are going to cover all Joins.
When we talk about (RDBMS) Relational database management systems, joins play a very important role. Joins are used to communicate with other tables in the database. Without learning or using joins it is highly impossible to connect with all tables and get all data we required.
Entity-Relationship Diagram (ERD): ER diagram is always very helpful in case of establishing joins between two or more tables.
ERD for DVD rental dataset.
Combining tables manually is time-consuming. Luckily, SQL gives us an easy sequence for this: it’s called a JOIN.
JOINS are used with SELECT command, which helps us to retrieve data from various tables. And we can merge the Select and Joins statements together into a single command. Whenever we want to get records from two or more tables, we will execute the joins commands.
It is used to merge columns from one or more tables according to the data of the standard columns between connected tables. Usually, the standard columns of the first table are primary key columns and the second table columns are foreign key columns.
we have various types of joins which are as follows:
Inner join
Left join
Right join
Cross join
Full outer join
Natural join
Self-join.
1. INNER JOIN
let's consider this example to understand what joins are.
--Display the address whose city id is in between 30 and 190 and whose district is Texas.
SELECT a.address,c.city,c.city_id, a.district
FROM address a
INNER JOIN city c ON a.city_id=c.city_id
where (c.city_id between 30 and 190 ) and
a.district='Texas'
Let’s break down this command:
The first line selects all columns from our combined table. If we only want to select certain columns, we can specify which ones we want. we need to specify from which table the column is coming.
we are creating aliases for city c and a for address. so calling columns from those tables should be followed by table name(or table aliases if created) . example a.address means this column address coming from address table a. c.city_id means column city_id coming from table city c.
2. The second line specifies the first table that we want to look in, "address"
3. The third line uses JOIN to say that we want to combine information from "address" with "city".
4. The fourth line tells us how to combine the two tables. We want to match the "address" table’s city_id column with the "city" table’s city_id column.
5. We are giving two conditions, and based on those conditions data will be retrieved.
SELECT a.address,c.city,c.city_id, a.district
FROM address a
JOIN city c ON a.city_id=c.city_id
where (c.city_id between 30 and 190 ) and
a.district='Texas'
from the previous query, we removed the INNER word and still the result is the same. It means INNER JOIN and JOIN are the same. If we don't specify anything in front of join then by default it is an inner join.
What happens in the inner join?
The table below explains it self , when two tables joins with INNER JOIN , they join on the basis of common column values. T1.C2 = T2.C2
The first and last rows have matching values of C2. The middle rows do not match. The final result has all values from the first and last rows but does not include the non-matching middle row.
1. LEFT JOIN
In this example below join /inner join and left join are giving the same outcome whereas the right join gives a different outcome, as it is taking all possible entries from the right table.
-- Q. List all the stores with their address.
--Select * from store;
--Select * from address;
select s.store_id, a.address
from store as s
join address as a
on s.address_id = a.address_id;
-- Q. List all the stores with their address.
select s.store_id, a.address
from store as s
left join address as a
on s.address_id = a.address_id;
select s.store_id, a.address
from store as s
right join address as a
on s.address_id = a.address_id;
How left Join works?
What if we want to combine two tables and keep some of the unmatched rows?
SQL lets us do this through a command called LEFT JOIN. A left join will keep all rows from the first table, regardless of whether there is a matching row in the second table.
SELECT *
FROM T1
LEFT JOIN T2
ON T1.C2 = T2.C2;
SELECT *
FROM T2
LEFT JOIN T1
ON T2.C2 = T1.C2;
The first and last rows have matching values of C2. The middle rows do not match. The final result will keep all rows of the first table but will omit the unmatched row from the second table.
3. RIGHT JOIN
In the right join , preference is given to the right table. we will get all values from the right table based on the joining column. All values from both tables that are matching, plus all those that are not matched from the right table.
we can see one example it will clear the concept.
---- Q. List all the stores with their address.
select s.store_id, a.address
from store as s
Right join address as a
on s.address_id = a.address_id;
if we change the right join to left, it will just show two stores with store id and their addresses.
But the Right join is showing lot of addresses but only two store_id.
if we use right join it will take all the unmatched rows as well.
let's see
How Right join works
4. FULL JOIN or OUTER JOIN
FULL JOIN selects records that have a match in the left or right table. Think of it as the “OR” JOIN compared with the “AND” JOIN (INNER JOIN).
select s.store_id, a.address
from store as s
full join address as a
on s.address_id = a.address_id;
5.Cross Join
Sometimes, we just want to combine all rows of one table with all rows of another table. cross joins don’t require an ON statement. You’re not really joining on any columns!
A more common usage of CROSS JOIN is when we need to compare each row of a table to a list of values.
This is the best example to understand cross-join. 3 rows from shirts table and 2 rows from pants table.
when cross join, it will give 3*2= 6 rows and all possible combinations.
the syntax for a cross join
SELECT shirts.shirt color,
pants.Pants color
FROM shirts
CROSS JOIN pants;
5. UNION
Sometimes we just want to stack one dataset on top of the other. Well, the UNION operator allows us to do that.
Suppose we have two tables and they have the same columns. Basically to append two tables with the same number of columns and the same column name.
SQL has strict rules for appending data:
Tables must have the same number of columns.
The columns must have the same data types in the same order as the first table.
UNION removes duplicate rows.
SELECT *
FROM Table1
UNION
SELECT *
FROM Table2;
--gets all unique fim_id
SELECT film_id
FROM public.film_actor
UNION
SELECT film_id
FROM public.film_category
ORDER BY film_id;
6.UNION ALL
The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various SELECT statements (all rows are returned).
Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types.
UNION ALL does not remove duplicate rows.
--Gets all records, including repeat film_id
SELECT film_id
FROM public.film_actor
UNION ALL
SELECT film_id
FROM public.film_category
ORDER BY film_id;
7. JOIN with where clause
This join with the where clause is popular because it takes less time to write. There is no need for an ON clause.
Basically, this join is Inner join. We are stating a condition in which a clause joins if all the values from one table match to values from the second table.
SELECT a.address,c.city, c.city_id, a.district
FROM address a ,city c
where a.city_id=c.city_id
and (c.city_id between 30 and 190 )
and a.district='Texas'
Thank you..
Comments