DEV Community

Cover image for TYPES OF JOINS
Jedidah Ondiso
Jedidah Ondiso

Posted on

TYPES OF JOINS

In Power BI, joins are primarily used in Power Query (during the "Merge Queries" step) to combine data from two separate tables based on a common column or relationship. Power BI supports six main types of joins, each serving a specific data combination need:

1. Left Outer Join
This is the most common join. It keeps all rows from the first (left) table and adds matching data from the second (right) table. If a row on the left does not have a match on the right, Power BI will return null (blank) for those right-side columns.

2. Right Outer Join
This is the exact mirror of a Left Outer Join. It keeps all rows from the second (right) table and only the matching rows from the first (left) table.

Use case: When you want your secondary table to dictate the primary list of items and you only need matching history from your main table.

3. Full Outer Join
This join type returns all rows from both tables. If there is no match between the rows, Power BI will fill in null values on the missing side.

Use case: When you want a complete, comprehensive dataset containing every single record from both tables, with matches aligned where they exist.

4. Inner Join
This join returns only the matching rows that exist in both the left and right tables. Any rows that do not have a corresponding value in the other table are completely dropped from the result.

Use case: When you only want to analyze data where two sets of information perfectly intersect.

5.Left Anti Join
This join returns only the rows from the left table that have NO match in the right table.

Use case: Great for identifying discrepancies, such as finding customers who haven't placed an order, or products in your main inventory that aren't in your sales history.

6. Right Anti Join
This join does the opposite of the left anti-join; it returns only the rows from the right table that have NO match in the left table.

Use case: Isolating records that exclusively exist in the secondary table and share no intersection with your primary table.

Top comments (0)