Blog SQL Basics: Query Structure and Joins

SQL Basics: Query Structure and Joins

SQL Basics Query Structure and Joins

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!)

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).


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.

— — — — — — — — — — — — — –


customers ← LEFT TABLE


orders o ← RIGHT TABLE


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?

Want to see how Knowi can help?

Sign up here for a 15-minute demo where we will learn more about your use case and recommend a few features that may be a good fit for what you're trying to accomplish.

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email
About the Author: