Agenda
1. Introduction to SQL
2. Joins in SQL
3. Types of Joins
4. Frequently Asked Questions pertaining to Joins
5. Resources to practice SQL for free
INTRODUCTION IN SQL
In the current era, around 2.5 Quinten bytes of data are being generated every day. Thus, data plays a critical role in the decision-making aspect of Business Operations, and knowing how to handle data and use different kinds of databases is the need of the hour.
There are various kinds of databases such as relational databases, hierarchical databases, network databases, object-oriented databases, and many more.
So, SQL ( pronounced sequel) which stands for Structured Query Language, is the core of relational databases which is used for managing and accessing the databases. Data in a relational database is stored in the form of tables.
Terminologies to be familiar with in a SQL table:
Fields — It is a column that contains specific information about every record in the table. A table cannot have duplicate column names.
Record — It is also known as a row which represents an individual entry in the table. For example, in the above image, there are 7 rows in the table.
Key Field — It is a column that can be used to link one table with another table, commonly known as primary key (single column) when it is used to identify the rows uniquely in the table and composite key (2 or more columns)
Joins
Join clause is used to combine rows from two or more tables, based on a related column between the 2 tables being combined. These are used to extract data from tables that have a one-to-many relationship between them or a many-to-many relationship between them
TYPES OF JOINS
There are 4 common types of Joins in SQL. We’ll look at them one by one.
1. Inner Join — This join returns the records from the tables which have matching values in both tables.
Now we will write a query to list the names of Customers who are in the same city as the Salesman
select cust_name
from customer
join salesman
on salesman.city = customer.city
This is the resultant table of the above query showing the customer records where cities are same as that of the salesman
2. Left Join — This join returns all the records from the left table and the matching records in the right table and returns “Null” for unmatched values in the right table. Let us consider the same tables as before.
select customer.cust_name, salesman.city
from customer
left join salesman
on salesman.city = customer.city
order by customer.cust_name
This is the resultant table of the above query showing all customer names from the left table and only the matching cities name from Salesman table
3. Right Join — This join returns all the records from the right table and the matching records from the left table and returns “Null” for unmatched values in the left table. Let us consider the same tables as before.
select customer.cust_name, salesman.city
from customer
right join salesman
on salesman.city = customer.city
order by customer.cust_name
This is the resultant table of the above query showing all city names from the right table and only the matching customer names from Customers table
4. Full Outer Join — This join returns all the matching values from both the tables i.e A and B, all the values which were left from table A unmatched and all the values which were left in B unmatched.
select customer.Cust_name, salesman.city
from customer
full outer join salesman
on salesman.city = customer.city
order by customer.cust_name
This join returns all the matching values from both the tables i.e A and B, all the values which were left from table A unmatched and all the values which were left in B unmatched.
Frequently Asked Questions pertaining to Joins
Q1. What is the difference between an Inner join and Left Join?
A1. INNER JOIN will return rows when there is a match in both tables where as the LEFT JOIN will return all of the rows from the left table and any matching rows from the right table and if the match is not found, it will return “null”
Q2. What is the difference between a Left Join and Full Outer Join ?
A2. LEFT JOIN will return all of the rows from the left table and any matching rows from the right table and if the match is not found, it will return “null” value whereas FULL JOIN will return all the rows from the joined tables, whether they are matched or not. Thus it combines the functionality of LEFT JOIN and RIGHT JOIN.
Q3. What is a Self Join?
A3. Let us consider a Table.
This customer table stores all company customers’ names, the IDs of their departments, and the IDs of their managers.
select
e.Cust_name,
m.Cust_name
from
customer as e
left join customer as m
on e.ID = m.Manager_id
order by
e.Cust_name
This resultant table maps the customer id for each customer with the manager id for each customer. Thus names of all customers are taken from left table and then corresponding manager name based on the condition “e.ID = m.Manager_id” is returned
Q4. What is a cross join?
A4. It is used to generate a paired combination of each row of the first table with each row of the second table, and is also known as cartesian join. Let us consider the below table.
-- Method 1
select foods.Name,
company.Company_Name
from foods
cross join company-- Method 2
select foods.Name,
company.Company_Name
from foods, company
As seen from above image, a combination of food name with each company is shown via cross join (Cropped the resultant table)
Resources to Practice SQL for free
1. SQLZOO
2. HackerRank
3. Codecademy
4. SQL Shack
SQL is an important skill for a Data Scientist/Analyst/Engineer or anyone who is looking forward to make a career in Analytics/Data Science. You all can also check out TutorialsPoint, GeeksforGeeks, W3Schools, JavatPoint, KhanAcademy as well as checkout Edureka’s Youtube for free tutorials to start with. Hope you liked. If you’re starting out or looking for a career transition in Data Science, connect with me on LinkedIN and I’ll be more than happy to help. Few Interesting links: Introduction to Python: Just Practical Stuff : Part 1 Introduction to Python: Just Practical Stuff : Part 2 Key Questions for Data Analyst : Taken from a R1 interview conducted by LatentView Analytics
Comments