An INNER JOIN returns rows that match across tables; a LEFT JOIN keeps every row from the left table and fills columns from the right table with NULL where no match exists. The practical choice is which unmatched records you need to keep. These six queries use a fictional beekeeping co-op to show how that choice changes the result.
Set up the co-op tables
A join combines rows from related tables by matching values according to a condition. Here, each member can have an assigned apiary. The member_id column identifies one member in Members; the apiary_id column identifies one apiary in Apiaries. Members.apiary_id refers to Apiaries.apiary_id.
For consistency, assume these records:
| Members | Apiary ID |
|---|---|
| Ada | 10 |
| Ben | 20 |
| Cy | NULL |
| Apiaries | Apiary ID |
|---|---|
| River Meadow | 10 |
| Hill Orchard | 20 |
| School Garden | 30 |
A primary key uniquely identifies a row in a table; here, those are Members.member_id and Apiaries.apiary_id. A foreign key is a column that refers to a key in another table; here, it is Members.apiary_id. The examples focus on the displayed names and IDs. SQL join behavior is broadly shared, but syntax and edge behavior can differ by database; the cited PostgreSQL references document these examples.
Query 1: Match members to their apiaries with INNER JOIN
SELECT Members.name, Apiaries.name AS apiary
FROM Members
INNER JOIN Apiaries
ON Members.apiary_id = Apiaries.apiary_id;The ON condition defines a match: the member’s assigned apiary ID must equal an apiary’s ID. The output has two rows—Ada with River Meadow and Ben with Hill Orchard. Cy has no apiary ID, and School Garden has no member assigned, so neither appears. An INNER JOIN returns only matching row pairs.
#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.
Query 2: Keep every member with LEFT JOIN
SELECT Members.name, Apiaries.name AS apiary
FROM Members
LEFT JOIN Apiaries
ON Members.apiary_id = Apiaries.apiary_id;A LEFT JOIN preserves all rows from its left input, Members. The output has three rows: Ada and Ben still match their apiaries, while Cy appears with NULL in the apiary column. School Garden is omitted because it is not a member row from the preserved, left-hand table.
Query 3: Keep every apiary with RIGHT JOIN
SELECT Members.name AS member, Apiaries.name AS apiary
FROM Members
RIGHT JOIN Apiaries
ON Members.apiary_id = Apiaries.apiary_id;A RIGHT JOIN preserves all rows from its right input, Apiaries. The output has three rows: the two assigned apiaries have matching members, and School Garden appears with NULL in the member column. Cy is absent because this query does not preserve unmatched member rows.
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.
You can express the same preservation direction with a left join by reversing the table order:
SELECT Members.name AS member, Apiaries.name AS apiary
FROM Apiaries
LEFT JOIN Members
ON Members.apiary_id = Apiaries.apiary_id;Query 4: Keep unmatched rows from both tables with FULL JOIN
SELECT Members.name AS member, Apiaries.name AS apiary
FROM Members
FULL JOIN Apiaries
ON Members.apiary_id = Apiaries.apiary_id;A FULL JOIN keeps matched pairs and unmatched rows from both inputs. The output has four rows: two matches, Cy with a NULL apiary, and School Garden with a NULL member. These null-extended rows make gaps on either side visible. Some database systems use the spelling FULL OUTER JOIN; check your database’s documentation for supported syntax.
Free tools Windows power users keep installed
One-click scans. No signup required.
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.
Query 5: Make every possible pairing with CROSS JOIN
SELECT Members.name AS member, Apiaries.name AS apiary
FROM Members
CROSS JOIN Apiaries;A CROSS JOIN does not match on a key. It returns every possible member-apiary combination: with three members and three apiaries, this example produces nine rows (3 × 3). That is useful only when every pairing is genuinely wanted—for example, preparing a planning grid of all members and possible apiaries. It is not a substitute for a relationship-based join.
Query 6: Compare two members with a self-join
SELECT first_member.name AS member_one,
second_member.name AS member_two,
Apiaries.name AS shared_apiary
FROM Members AS first_member
JOIN Members AS second_member
ON first_member.apiary_id = second_member.apiary_id
AND first_member.member_id < second_member.member_id
JOIN Apiaries
ON first_member.apiary_id = Apiaries.apiary_id;A self-join joins a table to itself. The aliases first_member and second_member let the query refer to two roles played by rows from Members. The ID comparison prevents pairing a member with themself and reports each pair once. With the sample records, the result contains no rows because Ada and Ben use different apiaries and Cy has none. If two members shared an apiary, their pair would appear with that apiary’s name.
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
Choose the join by the rows you must retain
| Join | Rows preserved | Result for this example |
|---|---|---|
INNER JOIN |
Only matching pairs | 2 rows |
LEFT JOIN |
Every left-side row, plus matches | 3 rows |
RIGHT JOIN |
Every right-side row, plus matches | 3 rows |
FULL JOIN |
Every row from both sides, plus matches | 4 rows |
CROSS JOIN |
Every possible pairing; no matching condition | 9 rows |
For INNER, LEFT, RIGHT, and FULL joins, the counts follow from these specific sample records and the stated matching condition. In a left, right, or full join, an unmatched preserved row has NULL values for columns supplied by the other side.
Keep matching conditions separate from filters
Use an explicit JOIN ... ON condition to make the relationship visible, and qualify columns with table names or aliases when a name could be ambiguous. PostgreSQL’s join tutorial recommends qualifying column names as good style, and Microsoft’s SQL Server documentation describes joins as retrieving data from multiple tables based on logical relationships between them. PostgreSQL 16: Joins Between Tables · Microsoft Learn: Joins (SQL Server).
Recommended Free Tools
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.
For an outer join, the location of a filter changes which rows survive. To keep every member but show an apiary only when it is River Meadow, put the restriction in ON:
SELECT Members.name, Apiaries.name AS apiary
FROM Members
LEFT JOIN Apiaries
ON Members.apiary_id = Apiaries.apiary_id
AND Apiaries.name = 'River Meadow';This retains all three members; Ben’s apiary column becomes NULL because Hill Orchard does not meet the added match condition. If instead you write WHERE Apiaries.name = 'River Meadow', rows with a null apiary name are filtered out, so Cy no longer appears. Put a condition in ON when it limits eligible matches but must not remove preserved left rows; use WHERE when the final result should exclude rows that fail the filter.
Use USING or NATURAL only when their matching rules are intended
USING (apiary_id) is a concise alternative to an equality condition in ON when both tables have a column with that exact shared name:
SELECT Members.name, Apiaries.name AS apiary
FROM Members
JOIN Apiaries USING (apiary_id);NATURAL JOIN infers its matching columns from every column name shared by the two tables. That can make the join change if a future schema change adds another same-named column. Prefer an explicit ON or deliberate USING list when you want the relationship to remain clear. PostgreSQL 18: Table Expressions.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →What the SQL says—and what the database executes
The pair-matching explanation is a useful way to understand query results, not a claim that the database must literally compare every possible row pair for an ordinary join. PostgreSQL notes that execution is usually more efficient than the conceptual pairwise model. SQL Server documentation explains that the optimizer chooses physical algorithms and table order using factors such as table size, indexes, and data distribution. The six examples teach result semantics, not performance guarantees.
Quick Recap
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.
