SQL JOINs Break at the Boundaries — Test the Edge Cases Before They Hit Production

After running single-table queries smoothly, multi-table queries will soon appear. Business interfaces rarely involve only one table: users come with orders, orders come with payments, and statistics need to be grouped by user, status, and time. The word join itself is not difficult; what really causes problems is which table the result rows ultimately start from, which fields will be filled with NULL, whether conditions are placed in on or where, and whether the statistics count users, orders, or the result rows after joining.

Moving from MySQL to KingbaseES, there's no need to chase complex scenarios right away. First, use three small tables to clearly understand the result boundaries, which is more useful than memorizing a list of JOIN types.

Also, here are some recent event recommendations:

1. Recommend Business Opportunities, Win Gifts — Kingbase Community "Peer Plan" Launched (https://bbs.kingbase.com.cn/forumDetail?articleId=1d09d598f414ab764eda4907e8f54758) 2. 2026 Kingbase Database Intelligent O&M Tool Development Contest (https://bbs.kingbase.com.cn/forumDetail?articleId=2394013b19f3ef84a43edb994692b88e) (https://bbs.kingbase.com.cn/forumDetail?articleId=6152608d769b472397ccfbd29879c0bd)

First, Prepare Three Small Tables

This time, create three tables: t_join_user, t_join_order, t_join_payment. The fields are all ordinary: the user table stores users and statuses, the order table stores order numbers, amounts, and order statuses, and the payment table stores payment statuses and payment amounts.

No foreign keys are added here. This is not a suggestion to design business tables this way, but rather to introduce two types of incomplete data: the order table has a row with user_id = 6, but the user table does not have this user; the payment table has a row with order_id = 106, but the order table does not have this order. Without this kind of boundary data, left join, right join, and full join would look too smooth, and many pitfalls would not be exposed.

The amount of inserted data is also not large. The user table has 5 rows: alice, bob, cindy, david, eric. The order table has 5 rows: alice has two orders, bob and cindy each have one, and one order is linked to a non-existent user 6. The payment table has 4 rows: orders 101 and 103 are paid successfully, 104 failed, and another payment record is linked to a non-existent order 106.

The focus of this data is not to simulate real business, but to make three situations appear simultaneously in the join results: matching, left side has data but right side doesn't, right side has data but left side doesn't. The differences between JOINs are mainly reflected in these boundaries.

The table names all have the t_join_ prefix to distinguish them from the test tables used in previous single-table queries. Field names also deliberately retain several parts that are prone to duplication, such as the user table having user_id and the order table also having user_id; the order table having order_id and the payment table also having order_id. Once fields have the same name in multi-table queries, writing bare fields directly will quickly become unclear, so the following SQL uses u, o, p as aliases.

INNER JOIN Only Keeps Successfully Matched Rows

Start with the most common inner join:

select
  u.user_id,
  u.user_name,
  o.order_id,
  o.order_no,
  o.order_status,
  o.amount
from app_schema.t_join_user u
inner join app_schema.t_join_order o
  on u.user_id = o.user_id
order by u.user_id, o.order_id;

The result is only 4 rows. alice appears twice, corresponding to orders 101 and 102; bob corresponds to order 103; cindy corresponds to order 104. david and eric have no orders and are not returned. Order 105 has user_id 6, which cannot be found in the user table, so it is also not returned.

This is the most basic rule of inner join: only when both sides can connect does the data enter the result set. A one-to-many relationship will expand the main table rows; alice has only one row in the user table, but becomes two rows after joining with orders. When doing statistics later, this expansion will directly affect the result of count(*).

Inner joins are suitable for answering "which records have already been associated." For example, if you only care about users with orders or orders that can find a user, inner join is very clean. But if an interface needs to query a user list and also display order information, using an inner join will swallow users without orders. SQL doesn't report an error, but people are missing from the page — this kind of problem is more subtle than a syntax error.

LEFT JOIN Will Preserve the Left Table

If you want to query all users and whether they have orders, an inner join is insufficient. Switch to a left join:

select
  u.user_id,
  u.user_name,
  u.status,
  o.order_id,
  o.order_status,
  o.amount
from app_schema.t_join_user u
left join app_schema.t_join_order o
  on u.user_id = o.user_id
order by u.user_id, o.order_id;

This time, 6 rows are returned. alice still appears twice due to two orders; bob and cindy each have one row; david and eric, although without orders, are also preserved, only with order-related fields being empty. Order 105 will not appear because the left table of this SQL is the user table.

This result is easier to remember than the definition: starting from the user table, users are not lost; orders are brought out if they can match, and filled with NULL if they cannot. This pattern is used in many list pages, admin pages, and report base tables.

The NULLs here should also be seen as part of the result. The order fields for david and eric are empty, which does not indicate a query error but precisely shows that "the user exists but has no matching order." If followed by Java code, MyBatis mapping, or interface JSON, the right table fields must allow nulls. Forcefully converting these NULLs back into default objects can easily mislead the caller into thinking there really is an empty order.

Conditions Placed in ON vs. WHERE Are Not the Same Thing

The most error-prone part of outer joins is not the left join itself, but where the right table conditions are placed.

First, put the order status condition in on:

from app_schema.t_join_user u
left join app_schema.t_join_order o
  on u.user_id = o.user_id
 and o.order_status = 'paid'

The result returns 5 rows, all 5 users are still there. alice only matches the paid order 101, and the unpaid order 102 no longer appears as an order row; bob matches 103; cindy, david, eric are all preserved, just with empty order fields.

Put the same condition in where:

from app_schema.t_join_user u
left join app_schema.t_join_order o
  on u.user_id = o.user_id
where o.order_status = 'paid'

The result is only 2 rows: alice's 101 and bob's 103. Users without paid orders are all filtered out.

This step is very common in real development. If the requirement says "query all users and display paid orders," the condition should be placed in on; if the requirement says "only query users with paid orders," placing the condition in where is more direct. The two SQL statements look only one line apart, but the result changes from 5 rows to 2.

Don't crudely memorize this as a fixed mantra. It's more reliable to look directly at the execution result: on controls how the right table matches, and where filters the result after joining. Those rows where the right table fields are NULL naturally cannot survive when encountering where o.order_status = 'paid'.

This difference is especially obvious in order status filtering. If the requirement is written as "all users must be displayed, only look at their paid orders," using on is more semantically correct; if the requirement is "only display users with paid orders," using where is more semantically correct. Both are paid, but the business semantics are different, so the SQL position is different. Clarifying this sentence before writing saves a lot of rework compared to looking at the result afterward.

RIGHT JOIN and FULL JOIN Show Boundaries More Intuitively

right join can look at user matching from the perspective of the order table:

from app_schema.t_join_user u
right join app_schema.t_join_order o
  on u.user_id = o.user_id

The result returns 5 rows from the order table. Orders 101, 102, 103, 104 can find users, but order 105 cannot find a user, so the user-side fields are empty. david and eric from the user table will not appear because the right table is the order table.

Now look at full join:

from app_schema.t_join_user u
full join app_schema.t_join_order o
  on u.user_id = o.user_id

The result returns 7 rows. The 4 successfully matched rows appear normally; david and eric are preserved as data with only users and no orders; order 105 is also preserved as data with only an order and no user.

MySQL users usually write left join more often; right join can also be understood by switching the direction. full join is worth running separately because it can simultaneously see unmatched data on both sides. When doing data reconciliation, this result is very intuitive: wherever there is a gap, NULL appears on the other side.

right join doesn't necessarily have to be used in daily code. Many teams rewrite right joins as left joins just to make the main table read more smoothly from left to right. The use of full join is more for reconciliation and troubleshooting; it's not needed for every list interface, but when encountering scenarios where "both sides may have orphan data," it's more direct than piecing together two left join results.

Three-Table LEFT JOIN Is Affected by the Starting Point

Now connect the payment table:

select
  u.user_id,
  u.user_name,
  o.order_id,
  o.order_status,
  o.amount,
  p.payment_id,
  p.pay_status,
  p.pay_amount
from app_schema.t_join_user u
left join app_schema.t_join_order o
  on u.user_id = o.user_id
left join app_schema.t_join_payment p
  on o.order_id = p.order_id
order by u.user_id, o.order_id, p.payment_id;

The result still starts from the user table. alice's 101 has a successful payment, 102 has no payment; bob's 103 has a successful payment; cindy's 104 has a failed payment; david and eric have no orders, so order and payment fields are all empty.

The row payment_id = 1004 in the payment table did not appear. It is linked to order_id = 106, but order 106 does not exist in the order table; this SQL also left joins from the user table all the way to orders and payments, so the orphan payment is not brought out.

This shows that in three-table joins, you cannot just look at the JOIN type; you also need to see which table the query starts from. User-dimension queries are suitable for starting from the user table; order and payment reconciliation should not rely on this SQL to find all payment anomalies.

There is another habit to keep for three-table joins: prefix every field with its alias. u.user_id, o.order_id, p.payment_id look a bit more verbose than bare fields, but they save a lot of guesswork during troubleshooting. Especially when the query result contains both order status and payment status, it's important to distinguish order_status and pay_status. Payment success does not mean the order status is necessarily correct, and an order being paid does not mean a record necessarily exists in the payment table.

For Order and Payment Reconciliation, FULL JOIN Is More Suitable

Do a separate full join on the order table and payment table:

select
  o.order_id as order_id_from_order,
  o.order_no,
  o.order_status,
  p.payment_id,
  p.order_id as order_id_from_payment,
  p.pay_status,
  p.pay_amount
from app_schema.t_join_order o
full join app_schema.t_join_payment p
  on o.order_id = p.order_id
order by coalesce(o.order_id, p.order_id), p.payment_id;

This time the result has 6 rows. Orders 101, 103, 104 can match payment records; orders 102 and 105 have no payments, so the payment side is empty; payment 1004 corresponds to a non-existent order 106, so the order side is empty.

This type of SQL is very suitable for reconciliation. A left join can only emphasize one side; full join can put the gaps from both sides in the same result. When formally designing business tables, you can certainly rely on foreign keys, state machines, and business validation to reduce such anomalies, but when troubleshooting historical data, being able to directly find the gaps saves a lot of effort.

Here, 102 and 105 are "orders without payments," and 1004 is "payment without an order." Two types of problems appear in the same result set, and can then be processed separately by adding conditions: check payment anomalies where the order side is empty, and check unpaid orders where the payment side is empty. This is easier to align the scope than writing one query for orders and another for payments.

After JOIN, Confirm What You Are Counting First

After a one-to-many join, the statistical scope is easily confused. First, count the number of orders and paid orders per user:

select
  u.user_id,
  u.user_name,
  count(*) as joined_rows,
  count(o.order_id) as order_count,
  count(case when o.order_status = 'paid' then 1 end) as paid_order_count,
  coalesce(sum(o.amount), 0) as total_amount
from app_schema.t_join_user u
left join app_schema.t_join_order o
  on u.user_id = o.user_id
group by u.user_id, u.user_name
order by u.user_id;

alice's joined_rows is 2 because she has two orders; order_count is also 2; paid orders are only 1; the total amount is 258. david and eric have no orders, but after the left join they still each have one row, so joined_rows is 1, order_count is 0, and the amount is converted to 0 using coalesce.

Now look at a total count without grouping by user:

select
  count(*) as joined_rows,
  count(u.user_id) as user_id_count,
  count(distinct u.user_id) as distinct_user_count,
  count(o.order_id) as order_count
from app_schema.t_join_user u
left join app_schema.t_join_order o
  on u.user_id = o.user_id;

The result is joined_rows = 6, user_id_count = 6, distinct_user_count = 5, order_count = 4.

These numbers just break down the scope. count(*) counts the number of rows after joining; count(u.user_id) is also 6 because the left table user field has a value in every row; count(distinct u.user_id) is the deduplicated user count; count(o.order_id) counts the orders that can be matched from the user table, and order 105 is not in this left join result, so it only counts 4.

Many reporting problems arise right here. The SQL itself doesn't report an error, and the result is not empty, but the counting target is wrong. The interface needs the user count, but count(*) is written; it needs the order count, but rows are directly counted from the user left join result. After JOIN, clarifying "what to count" first saves more time than fixing the data later.

This set of results also exposes a common misunderstanding: users without orders do not disappear after left join, but each occupies one row. So david and eric's joined_rows are both 1, and order_count is 0. As long as the report mixes the full set of the main table with statistics from the detail table, count(*) cannot be written casually. If you need to count the main table deduplicated, write count(distinct u.user_id); if you need to count orders, write count(o.order_id); if you need to count paid orders, put the status condition inside the aggregate expression.

EXISTS Is Suitable for Just Checking Existence

Finally, use exists for a comparison. If you only want to query "users with orders," you don't necessarily need to JOIN and then deduplicate:

select
  u.user_id,
  u.user_name,
  u.status
from app_schema.t_join_user u
where exists (
  select 1
  from app_schema.t_join_order o
  where o.user_id = u.user_id
)
order by u.user_id;

The result returns three users: alice, bob, cindy. david and eric have no orders and are not returned; order 105 is linked to a non-existent user 6 and will not affect the user table result.

exists does not replace JOIN. It is suitable for answering "are there matching records" type questions; JOIN is suitable for querying fields from both sides together. When writing interface SQL, you can first determine whether the requirement needs fields or just existence. If only existence is needed, exists expresses it more directly.

This SQL does not return order fields, nor does it return alice twice because she has two orders. It only checks whether a matching record exists in the subquery, and users meeting the condition are returned once. When encountering requirements like "query users with orders," "query orders with payment records," "query accounts already bound to roles," exists is often closer to the problem itself than JOIN followed by distinct.

At this point, several basic boundaries of multi-table queries are clear. inner join only keeps matching rows; left join preserves the left table; right join looks at the right table in reverse; full join simultaneously preserves unmatched data on both sides; whether outer join conditions are placed in on or where directly changes the result; after a one-to-many join, count(*) no longer naturally equals the main table count. For developers coming from MySQL, the JOIN syntax itself is not unfamiliar, but these result boundaries must be tested in practice; otherwise, it's easy to rely on intuition when writing complex SQL later.