SQL joins combine rows from tables, but the join type determines which rows survive. Use INNER JOIN when only matches belong in the result; use an outer join when unmatched rows from one or both inputs must remain. A join can also multiply rows when one row matches several rows, and a WHERE condition can remove rows an outer join would otherwise preserve.
How a SQL join combines rows
A join takes rows from two inputs and forms pairs according to a condition. For example, a customer row and an order row pair when their customer IDs match. The selected columns from each qualifying pair appear together in the result.
Consider customers(customer_id, name) and orders(order_id, customer_id). In the query below, ON states which customer and order rows match:
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.customer_id;This returns customer/order pairs with equal customer IDs. A customer without an order is absent, as is an order with no matching customer. SQL Server documentation describes these as logical join semantics; the database engine separately chooses how to execute a join, such as with a nested-loops, merge, hash, or adaptive algorithm. The execution method depends on the engine and factors including data size, indexes, and distribution—not simply on whether the query says LEFT or INNER. Microsoft Learn: Joins (SQL Server)
Recommended Free Tools
#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
Which join type should you use?
| Join type | Rows retained | Typical purpose |
|---|---|---|
INNER JOIN |
Only pairs that meet the join condition | Show entities that have a related row on both sides |
LEFT JOIN or LEFT OUTER JOIN |
Every left-side row, plus matching right-side values; unmatched right-side columns are NULL | Keep all rows from the primary input while adding optional details |
RIGHT JOIN or RIGHT OUTER JOIN |
Every right-side row, plus matching left-side values; unmatched left-side columns are NULL | Preserve the right input when it is the required side |
FULL OUTER JOIN |
Matching pairs and unmatched rows from both inputs; absent-side columns are NULL | Reconcile two sets without dropping records unique to either set |
CROSS JOIN |
Every possible pair of input rows | Deliberately generate combinations |
INNER JOIN: keep matches only
Choose INNER JOIN when rows without a partner should not appear. In a customer-and-orders report, it lists customers with at least one matching order, paired with those orders. It does not mean one output row per customer: a customer with several orders appears in several pairs.
LEFT JOIN: preserve the left input
Choose LEFT JOIN when every row from the table or result on the left must survive, whether or not it has a match on the right:
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id;A customer with no matching order still appears, with NULL in the selected order column. A customer with multiple orders appears once per matching order.
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
RIGHT JOIN and FULL OUTER JOIN: preserve the other side or both
RIGHT JOIN applies the same preservation rule to the right input: all right-side rows remain, with left-side columns set to NULL where no match exists. FULL OUTER JOIN retains unmatched rows from both inputs as well as matching pairs. Use it when comparing or reconciling two sets and neither side’s unmatched records should be discarded. These terms describe logical results; availability and syntax details can vary by database system, so consult the documentation for your engine when portability matters. The preservation and NULL-extension behavior is also described in the PostgreSQL table expressions manual mirror.
CROSS JOIN: make every combination
A CROSS JOIN pairs every row on the left with every row on the right; it does not require a matching key. If the inputs contain m and n rows, the result contains m × n pairs. This is useful when all combinations are intended, such as pairing each item with each scenario. If the result is unexpectedly large, check whether a cross join—or a missing or ineffective join condition—created combinations you did not intend. SQLite’s official documentation also describes join processing using Cartesian products: SQLite SELECT documentation.
Why a join can repeat rows
A join returns qualifying pairs, not a promise of one row per input row. If one customer matches three orders, the result contains three customer/order pairs. The customer columns therefore repeat; that is expected for a one-to-many relationship, not automatically a data error.
Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Before interpreting counts or labeling repeated values as duplicates, check the relationship and the keys:
- Is the join key unique on the side you expected to have one row per key?
- Can a row on either side legitimately match multiple rows on the other side?
- Does the selected output include enough columns to distinguish separate matching pairs?
- Are you counting joined rows when you mean to count distinct customers or another entity?
If the intended relationship is one-to-one but the join produces multiple matches, inspect the data and join condition for repeated keys or an incomplete match condition. Do not remove rows with DISTINCT until you know whether the pairs are genuinely redundant; doing so can hide a relationship or query problem.
Why a LEFT JOIN returns NULLs
With an outer join, NULL in right-side output columns can mean no right-side row matched. But those columns may also be NULL in a matching row’s source data. SQL Server documentation warns that these cases can be hard to distinguish in a result, so test a right-side identifier that is guaranteed non-NULL for real rows—not an optional field.
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
For example, to find customers with no orders, test order_id if it identifies a real order and cannot itself be NULL:
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;The outer join supplies NULL for o.order_id when no order matches; the WHERE condition selects those rows. If the tested column is nullable for actual orders, this query can also include matched orders whose identifier is NULL, so choose a reliably non-NULL key.
Do not assume that two NULL join keys match each other. In the documented SQL Server behavior, equality comparisons involving NULL do not establish a match. An outer join can then add its own NULLs for missing-side columns, on top of any NULL values already present in the source. See Microsoft’s explanation of joins and NULLs for SQL Server-specific behavior.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
How ON and WHERE affect an outer join
ON determines which rows match during the join. WHERE filters the result after the join. With an outer join, moving a condition between them can change which preserved rows survive.
Suppose all customers must remain, but only orders with a chosen status should be attached. Put the right-side condition in ON:
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'Open';Now a customer without an open order remains; the order columns are NULL when no order satisfies both parts of the match condition. By contrast, putting o.status = 'Open' in WHERE filters the joined result. A customer with no matching order has a NULL status, so that row does not meet the condition and is removed. That can make the query behave like an inner-filtered result for this condition.
Decide first whether you need to preserve left-side rows that lack a qualifying right-side match. If yes, keep the right-side qualification in ON; if not, a WHERE filter may express the desired result. Exact optimizer transformations differ by engine, but the distinction in requested result is logical.
Quick Recap
A quick way to diagnose a join result
- State which input must be preserved. Use
INNERfor matched pairs only, an outer join to retain unmatched rows from a chosen side, orFULL OUTERto retain both sides. - Verify the match condition. Check that the columns in
ONexpress the intended relationship and that NULL or repeated keys are understood. - Check row multiplication. Compare the key uniqueness and expected one-to-one, one-to-many, or many-to-many relationship on both sides.
- Separate missing matches from source NULLs. For an outer join, test a right-side key that cannot be NULL in a real row.
- Review filters on the optional side. A right-side predicate in
WHEREcan remove NULL-extended rows that a left join otherwise preserves. - Investigate unexpectedly large results. Check for an intentional
CROSS JOIN, a missing condition, or multiple matches per key.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
