Usage of (+) in sql

In SQL, the ( ) operator is used to merge query result sets, filling NULL values ​​in unmatched rows. It allows performing outer joins, avoiding Cartesian products, and comes in two types: left outer join and right outer join. Left and right outer joins will return all rows from the left or right table, filling in NULL values ​​for unmatched rows.

Usage of ( ) in SQL

In SQL query, ( ) operator is used to combine two The query result sets are merged and missing rows are filled with NULL values.

Syntax:

<code>SELECT column_list
FROM table1
LEFT|RIGHT (+) JOIN table2
ON join_condition;</code>

Function:

• Outer connection: ( ) operation operator allows you to perform an outer join, which returns a result set that contains matching rows from both tables and all rows from one or both tables.
• Fill missing rows: For unmatched rows, the () operator inserts NULL values ​​in the missing table.
• Avoid Cartesian product: In an inner join, if there are no matching rows, the query will return an empty result set. Using the ( ) operator you can fill in missing rows and avoid the Cartesian product.

Type:

• Left Outer Join: LEFT ( ) JOIN returns all rows from the left table and No matching left table rows in the right table are populated with NULL values.
• Right Outer Join: RIGHT ( ) JOIN returns all rows from the right table and fills NULL values ​​for right table rows that do not have a match in the left table.

Example:

Suppose we have the following two tables:

students
id	name
1	John
2	Mary

##courses##id123##The following query uses LEFT ( ) JOIN to join these two Tables are joined and missing rows are filled in:

<code>SELECT *
FROM students
LEFT (+) JOIN courses
ON students.id = courses.id;</code>

course_name
Math
Science
History

Result:

##idname1John##2 MaryScienceNULLNULLHistory

course_name
Math

The above is the detailed content of Usage of (+) in sql. For more information, please follow other related articles on the PHP Chinese website!