Festival Season OfferUp to 25% off on all our programmes — claim it before you enrol
← All Career InsightsData Analytics

What are SQL joins and how do I use them?

A SQL join combines rows from two tables using a column they share, such as a customer ID. An INNER JOIN keeps only rows that match in both tables; a LEFT JOIN keeps every row from the first table and fills gaps with NULL; RIGHT and FULL OUTER JOINs do the same from the other side or both sides. Data analysts use joins in almost every report.

Why joins are the heart of analyst SQL

Business data is stored in separate tables: customers in one, orders in another, products in a third. Almost every real question, such as "revenue by city last month", needs columns from more than one of them.

Joins are how you bring them together. They are also where most wrong numbers in reports come from, so understanding exactly what each join keeps and drops is one of the most valuable SQL skills an analyst can have.

The six joins, with a simple example

Imagine a customers table and an orders table linked by customer_id.

INNER JOIN

Only customers who have placed orders, with their orders. Customers without orders and orders without a known customer are left out.

LEFT JOIN

Every customer, with their orders where they exist. Customers who never ordered appear once, with NULL in the order columns.

RIGHT JOIN

Every order, with customer details where they exist. Rarely needed, since you can swap the tables and use a LEFT JOIN.

FULL OUTER JOIN

Every customer and every order, matched where possible. Useful for finding records that exist on only one side.

CROSS JOIN

Every row of one table paired with every row of the other, for example every store with every month to build a calendar.

SELF JOIN

A table joined to itself, such as employees joined to their own managers in the same employees table.

How to write a join you can trust

A short routine that prevents most join mistakes.

  1. Know the grain of each table

    Decide what one row means in each table, such as one customer or one order line, before you join anything.

  2. Check the key is unique where it should be

    Run a quick COUNT(*) against COUNT(DISTINCT customer_id). If the "one" side has duplicates, the join will multiply rows.

  3. Pick the join from the question

    "Customers who bought" suggests INNER. "All customers, including those who did not buy" needs LEFT.

  4. Count rows before and after

    Compare the row count after the join with what you expected. A sudden jump is the clearest sign of duplicate keys.

  5. Filter in the right place

    With a LEFT JOIN, a condition on the right table in WHERE removes the unmatched rows. Put it in the ON clause if you want to keep them.

Join mistakes that give wrong totals

These slips produce numbers that look reasonable but are wrong:

  • Joining on a key that is not unique, which repeats rows and inflates sums.
  • Using INNER JOIN when the question needs everyone, silently dropping customers with no activity.
  • Filtering a LEFT JOIN's right table in WHERE, which quietly turns it into an INNER JOIN.
  • Forgetting that NULL never equals NULL, so rows with missing keys never match.
  • Summing a column from the "one" side after joining to the "many" side, counting the same value several times.

Quick answers about SQL joins

Short answers to the questions analysts search for most.

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns every row from the left table and fills the right table's columns with NULL where there is no match.

Which SQL join is used most by data analysts?

LEFT JOIN and INNER JOIN cover most day-to-day work. LEFT JOIN is especially common because reports usually need to keep every customer, product or date, even with no activity.

Why does my join return more rows than expected?

Usually because the joining column has duplicate values on one or both sides, so each match is repeated. Check uniqueness of the key before joining.

Does MySQL support FULL OUTER JOIN?

No. MySQL does not support FULL OUTER JOIN directly. You can get the same result by combining a LEFT JOIN and a RIGHT JOIN with UNION.

Are SQL joins asked in data analyst interviews?

Yes, very often. Expect to explain the join types and write a query that combines two or three tables, often with GROUP BY.

Go further with SQL for analysis

See where SQL sits in the syllabus, or read the guides and roles that build on it.

See the Data Analytics programmeSee the SQL for Data Analysis moduleSee the Excel for Data Analysis moduleSee the Domain Knowledge & Capstone moduleRead: is SQL required for a data analyst job?Role: Reporting AnalystRole: Analytics ConsultantBrowse all Career Insights

Practise joins on two small tables

Make a customers table and an orders table with a few deliberate gaps and duplicates, then run every join type and count the rows each time. Once you can predict the count before running the query, joins stop being risky.

Train for a Data Analytics role

The same programme, duration and fees, with the learning path built around one job role.

Data AnalystBI AnalystBusiness AnalystReporting AnalystAnalytics ConsultantDashboard Developer

Learn SQL for analysis with a mentor

Ask our team how the Data Analytics programme in Madhapur, Hyderabad teaches SQL, Excel and Power BI with real datasets.

Our admissions team will call you back within 90 minutes.
AddressHouse No. 3-535, 3rd Floor A Section, LR Towers, 100 Feet Road, Ayyappa Society, Madhapur, Hyderabad, Telangana 500081