_edited_edited.png)
SQL (Structured Query Language) is a powerful tool for managing and manipulating relational databases. SQL can join tables.
Joins is a clause which is combining data or rows from two or more different tables based on combined field between them. Joins can fetch data from multiple databases and combine it in one table.
These are types of joins:
We have two tables, Employee Table and Department Table.
Table 1
Employees Table
Table 2
Department Table
Let’s explore different types of joints:
Inner Join: Returns all rows which are matches from both tables. We have two tables, Tables for employees and departments. We now only want to view employees who are part of the department for which Inner Join is used.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEE
INNER JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID.
First Name | Department Name |
Tom | Finance |
John | Marketing |
Sara | Marketing |
Lara | Accounts |
Mary | HR |
Left Join: Returns all rows from left table which matches data with right table, if there is no match it shows Null values from right table.
If we want to see all the employees who do not belong to any department we use Left join.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID;
First Name | Department Name |
Tom | Finance |
John | Marketing |
Sara | Marketing |
Mark | NULL |
Lara | Accounts |
Mary | HR |
Right Join: Returns all rows from right table which matches data with left table, if there is no match from left table it shows Null value.
Right Join is used to display all departments without any employees.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEE
RIGHT JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID;
First Name | Department Name |
Tom
| Finance |
John | Marketing |
Sara | Marketing |
Lara | Accounts |
Mary | HR |
Null | Null |
Full Outer Join: Returns all rows from both the tables, if there is no match then it shows Null values for missing part.
When we see all the employees and all the departments then we use full outer join.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEE
FULL OUTER JOIN DEPARTMENT ON EMPLOYEE.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID;
First Name | Department Name |
Tom
| Finance |
John | Marketing |
Sara | Marketing |
Mark | Null |
Lara | Accounts |
Mary | HR |
Null | Null |
Self-Join: A self-join is when a table joins within itself.
We use this when we want to compare rows within the same table, for example finding employees and managers at the same table.
Select E1. First name as Employee_Name, E2. First name as Manager_Name
From Employee E1
Inner Join Employee E2 on E1. Manager_ID= E2. Employee_ID
Emp.1 | Emp.2 | Dept. ID |
John | Sara | 20 |
Sara | John | 20 |
Now, we have covered joins in SQL, let's look at how to do them in Power BI
SQL Joins(Merge) in Power Bi:
Power BI is a tool which helps us to create report and analyze the data. When we have data in different tables, and we want to join then together then Power BI is using ‘Joins’ or ‘Merge’.
In Power BI there is one feature which is called 'Power Query Editor' where we can merge data from multiple tables. We can do this with the Merge query option.
To use this, go to the Home tab, we can see Merge Query dropdown. Once we click that we can see two options:
1.Merge Queries: Combine data into current queries.
2.Merge Queries as New: create a new table with combined data, leaving the original query unchanged.
If you choose ‘Merge Queries as New,’ a new window will pop up, that allows you to select the queries you want to merge and specify the matching columns. This is particularly useful when you want to combine data from multiple sources and create a new dataset without altering the original queries.
There are two tables, Table 1,Employees table and Table 2,Department Table.
Table 1
Employees Table
Table 2
Department Table
Left Join in Power BI
To perform Left Join in Power BI,
1. Click on Table 1(Employee Table)
2. Click on Table 2(Department Table)
3. Choose the Department ID column, because it is common in both tables.
4. After selecting Department ID, then Left join from join options.
5. Then click on OK.
Now, we can see Table 1, which is the Employees table. In the section labeled “No preview is available" ,in that we can select Table 2 i.e. Department table.
First select Dept ID from Table 1 and Dept ID from Table 2 and then select the left outer join and then select OK.
Once we click on Ok,” Merge 1” new table and 'Table 2 'column are created.
We can rename Merge 1 with Employees Information.
Now we will expand Table 2
Three options will appear after choosing Table 2: Select All Columns, Dept ID, and Dept Name. You can choose Department Name here as we have already selected Dept ID as the common column for the join. By adding only the necessary columns, this helps prevent redundancy and maintains the combined table's cleanliness.
This Employees Information Table.
Right Join in Power BI
To perform Right Join in Power BI
1. Click on Table 1(Employee Table)
2. Click on Table 2(Department Table)
3. Choose the Department ID column, because it is common in both tables.
4. After selecting Department ID, then Right join from join options.
5. Then click on OK.
This combines all the data from the Department table and matching rows from the Employee table based on Department ID.
Inner Join in Power Bi
To perform Inner Join in Power BI
1. Click on Table 1(Employee Table)
2. Click on Table 2(Department Table)
3. Select the Department ID column, because it is common at both tables.
4. After selecting Department ID, select Inner join from join options.
5. Then click on OK.
This combines all common data from the Employee table and Department Table based on Department ID.
Full Outer Join in Power Bi
To perform Full Outer Join in Power BI
1. Select Table 1(Employee Table)
2. Select Table 2(Department Table)
3. Select the Department ID column, because it is common at both tables.
4. After selecting Department ID, select Full outer join from join options.
5. Then click on OK.
This combines all data from the Employee table and Department Table.
Here we have covered Merge in Power BI.
Conclusion:
Understanding the links in SQL and Power BI is essential for analyzing data effectively. SQL can combine and query data directly from database, while power BI is easy visual way to do the same tasks, even if you are not familiar with SQL. By learning how to use joins in both tools, you can gain deeper insights from your data and make better decisions.
Comments