Sql Joins Power Bi

January 21, 2021

SQL-Joins-PowerBI

Github Repo
Explaining SQL JOINs with Power BI

` Power BI Dashboard - SQL JOINs`

lets now discuss about SQL JOINs

Here are the two Tables using to explain SQL JOINs

` Consumers Table `

Transcation Table

SQL JOINs

Here are the some basic SQL JOINs presented here ` MYSQL`

1. Join(Inner join
2. Left Join
3. Right Join
4. Full outer Join

Basic Concept of Join

Join statement helps pull the data from multiple tables.

If we want to pull the data from a table, then what we do? (Basic query)

SELECT the column names
 FROM that table

Now if we want to pull the data from two tables, then?*

We need to JOIN the tables ON relation among the tables

SELECT the columns from both tables (prefix with their table identification)
 FROM table1
  JOIN table2
   ON specifying the relation between the two tables 

If we want to pull the data from 3 or more tables - the process same

SELECT the columns from both tables (prefix with their table identification)
 FROM table1
  JOIN table2
   ON specifying the relation between table1 & table2
    JOIN tables3
     ON specifying the relation between table2 & table3

Types of SQL JOINs

1) JOIN(INNER JOIN)

JOIN = INNER JOIN (normally JOIN means INNER JOIN)

It only pulls the common data (available in both the tables) like intersection

MYSQL

SELECT c.id, c.name, t.quantity, t.amount
 FROM consumers c
  JOIN transcation t
    ON c.id = t.id

Table we get (output)

2) Left Join

Left Join pulls all the records of left table(Consumers Table) and common(Matching) data from right table(Transcation table)

MYSQL

SELECT c.id, c.name, t.quantity, t.amount
 FROM consumers c
  LEFT JOIN transcation t
    ON c.id = t.id

Table we get (output)

3) Right Join

Right Join pulls all the records of Right table(Transcation Table) and common(Matching) data from Left table(Consumer table)

MYSQL

SELECT c.id, c.name, t.quantity, t.amount
 FROM consumers c
  RIGHT JOIN transcation t
    ON c.id = t.id

Table we get (output)

4) Full outer Join

In mysql there is no full outer join so we need to use UNION

Full outer Join pull all the data from both the tables

MYSQL

SELECT c.id, c.name, t.quantity, t.amount
 FROM consumers c
  Left JOIN transcation t
    ON c.id = t.id
UNION
SELECT c.id, c.name, t.quantity, t.amount
 FROM consumers c
  Right JOIN transcation t
    ON c.id = t.id

Table we get (output)

and some more advanced joins - Self join, Union all, Cross join. soon.. I will ✌️