The join condition can be specified in the From or WHERE clause, and it is recommended that the join condition be specified in the FROM clause. The WHERE and having clauses can also contain search criteria to further filter the rows selected by the join condition.
Connections can be divided into the following categories:
internal connections. (typical join operation, using a comparison operator like = or <>). Including equal connections and natural connections .
An inner join uses a comparison operator to match rows in two tables based on the values of the columns that are common to each table. For example, retrieve all lines of the students and courses table with the same student identification number.
External Connection。 An outer join can beleft outward connection, right outer join, or full outer join
.
When you specify an outer join in the FROM clause, you can specify it by one of the following sets of keywords:
Left JOIN or left OUTER join.
The result set for the left outer join includes all rows from the left table specified in the outer clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.
Right join or right OUTER join.
The right outward connection is a reverse connection of the left outward connection. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
Full join or full OUTER join.
A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
Cross Connect. The cross join returns all the rows in the left table, and each row in the left table is combined with all the rows in the right table. Cross joins are also called Cartesian product.
For example, the following inner join retrieves a publisher residing in the same state and city as the
Use pubs
SELECT A.au_fname, A.au_lname, P.pub_name
From authors as a INNER JOIN publishers as P
On a.city = p.city
and a.state = P.state
ORDER by a.au_lname ASC, a.au_fname ASC
The table or view in the FROM clause can be specified in any order by an inner join or a full outer join, but the order of the table or view is important when you specify a table or view with a left or right outward connection. For more information about using left or right outward joins to arrange tables, see Using outer joins.
Example:
A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
Relationship between a.ID and parent_id
Internal connection
Select a.*,b.* from a inner join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Left connection
Select a.*,b.* from a LEFT join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu Null
Right connection
Select a.*,b.* from a right join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
Fully connected
Select a.*,b.* from a full join B on a.id=b.parent_id
The result is
1 Sheets 3 1 23 1
2 Lee 42 34 2
Null 3 34 4
3 Wang Wu Null
SQL left outer connection, right outer connection, full connection, inner connection
