How to Combine Data from Two Tables Using SQL's LEFT OUTER JOIN?

SQL: Joining Two Tables

In data analysis, it's often necessary to combine data from multiple tables. One such operation is joining two tables. In this example, let's consider two tables, A and B, which have the following data:

TABLE A

uid  name
1    test1
2    test2
3    test3
4    test4

TABLE B

uid  address
1    address1
2    address2
4    address3

Query to Fetch the Combined Result

The objective is to obtain a result table that combines the corresponding rows from tables A and B based on a common column:

RESULT

uid  name  address
1    test1  address1
2    test2  address2
3    test3  NULL
4    test4  address3

In SQL, this can be achieved using a LEFT OUTER JOIN. Here's the query:

SELECT A.uid, A.name, B.address
FROM A LEFT JOIN B ON A.uid=B.uid;

Explanation:

• The LEFT JOIN operator allows us to combine rows from table A with matching rows from table B.
• When a matching row is not found in table B for a row in table A, the corresponding columns in the result will be set to NULL.
• The query first selects the columns of interest from both tables.
• The JOIN clause specifies that rows should be combined based on the equality of uid columns in A and B.
• The LEFT OUTER JOIN ensures that all rows from table A are included in the result, regardless of whether they have matching rows in table B.

Additional Resources:

• Visual Representation of SQL Joins: https://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_V2.png
• Difference between JOIN and OUTER JOIN in MySQL: https://www.db-fiddle.com/f/g3nkjkd1y3iusvvqfe2843kg19

The above is the detailed content of How to Combine Data from Two Tables Using SQL's LEFT OUTER JOIN?. For more information, please follow other related articles on the PHP Chinese website!