The data we work with is rarely tucked neatly into one single table. Most of the time, it's scattered across different places, and pulling it all together is where SQL shines. When you know how to merge tables properly, you can get the full picture, whether you're analyzing customer data, product lists, employee records, or anything in between. Let's walk through the different ways you can merge tables in SQL so you're ready for whatever your database throws your way.

11 Ways to Merge Tables in SQL

INNER JOIN

If you only want the rows that have a match in both tables, INNER JOIN is the tool you need. It connects two tables based on a common column and only keeps the records where the connection exists. No match, no row — simple as that. If you’re looking for, say, customers who have placed orders, this is the join you'd pick.

LEFT JOIN (or LEFT OUTER JOIN)

There are times when you want everything from your first table, even if it doesn’t have a matching record in the second one. LEFT JOIN brings all the rows from the left-hand table and pulls matching data from the right-hand table where it can. If there’s no match, the missing fields show up as NULL. Think of it as giving the left table priority.

RIGHT JOIN (or RIGHT OUTER JOIN)

RIGHT JOIN flips the focus. Now, the right-hand table gets full attention. Every row from the right table appears in the result, and matching records from the left table are pulled where they exist. Missing information from the left will show up as NULL. It’s not used as often as LEFT JOIN but comes in handy when your main data set lives on the right side.

FULL JOIN (or FULL OUTER JOIN)

Sometimes, you just want everything, matched or not. FULL JOIN takes all the rows from both tables and pairs them up where it can. When no match exists, it still includes the rows but fills in the gaps with NULLs. It's perfect when you want a complete list that doesn't leave anything behind.

CROSS JOIN

Picture matching every row from Table A with every row from Table B. That’s exactly what CROSS JOIN does. It creates a cartesian product — a set of all possible combinations. If Table A has 5 rows and Table B has 4 rows, the result will have 20 rows. It’s powerful but can get big quickly, so use it when you truly need every pairing.

UNION

When you have two tables with the same structure — the same columns in the same order — and you just want to combine their results, UNION is the way to go. It stacks the results together into a single set, but it removes duplicate rows. If you have two lists of users and you want one clean list without repeats, UNION is your friend.

UNION ALL

UNION ALL works just like UNION, but it doesn't bother checking for duplicates. It simply combines everything. It's faster and keeps all records, even if they are identical. It's useful when you're gathering logs and events or when repeated entries aren't a problem.

SELF JOIN

Sometimes, the information you need lives inside a single table — it just needs to reference itself. A SELF JOIN lets you treat the table as if it were two separate tables and connect rows based on relationships within the data. A classic example is a table where each employee has a manager, and both employees and managers are listed in the same table.

MERGE Statement

In databases like SQL Server and Oracle, the MERGE statement allows you to combine update, insert, and delete actions in one step based on matches between two tables. You can think of it as a "smart" match: if a record exists, update it; if it doesn't, insert it, and if certain conditions apply, you can even delete it. It's a way to manage changes between tables without running several separate queries.

INSERT INTO … SELECT

When you need to move or copy rows from one table into another, INSERT INTO … SELECT handles it smoothly. It lets you take the results of a SELECT query and insert them straight into another table. Great for archiving data, making backup tables, or building summary tables without writing the same data out manually.

USING Clause

When your join condition is based on columns that have the same name in both tables, USING can make your code shorter and easier to read. Instead of writing a long ON condition like ON table1.customer_id = table2.customer_id, you just say USING (customer_id). It’s a small touch, but it can make long queries feel cleaner and less overwhelming.

Choosing the Right Way to Merge Tables

Every method for merging tables has its place, depending on the problem you're solving. If you need only the overlapping data, INNER JOIN fits perfectly. If your priority is not losing any records from a specific table, LEFT JOIN or RIGHT JOIN will cover you. FULL JOIN keeps the door open to all data. CROSS JOIN creates every possible combination. UNION and UNION ALL are about stacking data neatly, while SELF JOIN looks inside a single table for relationships. MERGE is useful for maintaining consistency between tables without juggling multiple queries. INSERT INTO … SELECT is ideal when you're setting up or refreshing tables. USING gives your query a cleaner look when the conditions are simple.

Conclusion

The more you work with these tools, the more naturally it becomes part of how you approach SQL. And the better you get at picking the right way to merge, the faster you can turn scattered tables into a story that actually makes sense.