TL;DR
This post gives a beginner-friendly intro to SQL—breaking down basic query structure (SELECT, FROM, WHERE, etc.) and explaining how joins work to combine data across tables. It also simplifies the four main join types (INNER, LEFT, RIGHT, FULL OUTER) so you can understand when and how to use each.
A simple google search will yield all sorts of detailed guides on SQL join types. Many of these guides are overwhelming with detail or assume the reader has advanced familiarity with SQL. For those of you who are fairly new to SQL, here is a brief introductory guide on SQL basics and join types to get you oriented.
(Note: Please share this with anyone who might find this helpful!)
Table of Contents
SQL Basics: Query Anatomy
Before defining the various join types, we need to understand the anatomy of a SQL query.
A SQL query refers to a block of code that defines what data you’d like to pull from your database. For data analysts, this is generally the start of any analysis. Here is a basic SQL query breakdown:
SELECT: Defines columns/fields to pull
FROM: Specifies table(s) to pull from
WHERE: Filters data on a condition (or conditions)
GROUP BY: Group rows based on one or more columns
ORDER BY: Define sort order
LIMIT: Limit the total number of records returned
Explanation: In this query, we are selecting all columns (SELECT *) from the customer_data table (FROM `customer_data`) for records that have a complete status (WHERE status = ‘complete’) and limiting records to 10,000 records (LIMIT 10000).
Joins
So, what is a join and where in the query does it belong?
Joins can be used in different portions of your query depending on your query structure and the SQL language you are using, but for now, we will focus on the basics.
Within a database, there are typically a collection of tables to pull from. For example, you may have a general customer table with customer_ids, customer_names, etc. You may have another table for orders that include customer_ids, order_ids, product_ids, quantities, order_dates, etc.
A join allows you to merge two or more tables’ data together based on join keys (a shared field that links the tables together).
The order it is written in within your query dictates which table is considered the ‘left’ table and the ‘right’ table. Joins are considered as part of the FROM portion of a SQL query and are executed from top to bottom.
— — — — — — — — — — — — — –
SELECT *
FROM
customers c ← LEFT TABLE
LEFT JOIN ← JOIN TYPE
orders o ← RIGHT TABLE
ON
c.customer_id = o.customer_id ← JOIN KEYS
— — — — — — — — — — — — — –
This query joins the orders table to the customers table based on the customer_id field that belongs to each table independently.
Here is a cheat sheet on join types:
INNER: Only includes records where join key overlaps
FULL OUTER: Includes all records from both tables
LEFT: Includes all records from LEFT table, and merges data from the right table where the right join key matches the left join key
RIGHT: Includes all records from RIGHT table, and merges data from the left table where the left join key matches the right join key
The diagram below is a visual representation of what data are being pulled based on the various join types with SQL query examples.
What questions do you have about SQL joins?
SQL JOIN Types Compared
Different SQL JOIN types control which rows appear in your result set. Here is a quick reference:
| JOIN Type | Returns | Typical Use Case |
|---|---|---|
| INNER JOIN | Only rows with matching values in both tables | Fetch orders that have a matching customer record |
| LEFT JOIN | All rows from the left table, matched rows from the right (nulls where no match) | All customers, including those with no orders |
| RIGHT JOIN | All rows from the right table, matched rows from the left | All orders, even if the customer record is missing |
| FULL OUTER JOIN | All rows from both tables, nulls where no match exists | Full reconciliation between two datasets |
| CROSS JOIN | Every combination of rows (Cartesian product) | Generating all size-color combinations for a catalog |
| SELF JOIN | A table joined to itself | Hierarchical data like employee-manager relationships |
Visualizing SQL Results with Knowi
Once you have your SQL query returning the right data, the next step is turning it into a dashboard. Knowi connects directly to SQL databases (MySQL, PostgreSQL, BigQuery, Snowflake) and lets you build dashboards without exporting to a spreadsheet. You write the query once, Knowi runs it on a schedule and keeps the visualization current.
Related reads:
See how Knowi connects to your SQL database and turns queries into live dashboards.
Frequently Asked Questions
What are the most common SQL commands?
The most common SQL commands are SELECT (retrieve data), INSERT (add rows), UPDATE (modify rows), DELETE (remove rows), CREATE TABLE (define a new table), and JOIN (combine rows from multiple tables). Most day-to-day work uses SELECT with WHERE filters and JOIN clauses.
What is the difference between INNER JOIN and LEFT JOIN in SQL?
INNER JOIN returns only rows where a match exists in both tables. LEFT JOIN returns all rows from the left table and the matching rows from the right table. Where no match exists on the right side, the columns from the right table appear as NULL. Use INNER JOIN when you only want complete matches, and LEFT JOIN when you need all records from the primary table regardless of whether a related record exists.
What is the difference between WHERE and HAVING in SQL?
WHERE filters rows before any grouping or aggregation happens. HAVING filters groups after a GROUP BY clause has been applied. For example, WHERE salary > 50000 filters individual employee rows, while HAVING AVG(salary) > 50000 filters department groups whose average salary exceeds that threshold.
What is the correct order of SQL query execution?
SQL processes clauses in this order: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT/OFFSET. Writing queries with this execution order in mind helps you understand why you cannot reference a SELECT alias inside a WHERE clause.
What is a SQL subquery?
A subquery is a SELECT statement nested inside another SQL statement. It can appear in the SELECT list, the FROM clause (as a derived table), or the WHERE clause. Subqueries are useful when you need to filter results based on an aggregated value, for example:
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees).