Intra and outer joins in SQL and cross-join usage

Source: Internet
Author: User
Tags joins

(i) Internal connections
SQL INNER JOIN keyword
When there is at least one match in the table, the INNER JOIN keyword returns a row.
The INNER JOIN query operation lists the rows of data that match the join criteria, which compares the columns of the connected column with the comparison operator.
The column value. The inner connection is divided into three kinds:
1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, whose query knot
The results list all the columns in the attached table, including the repeating columns.

Grammar

The code is as follows Copy Code
SELECT column_name (s)
From table_name1
INNER JOIN table_name2
On Table_name1.column_name=table_name2.column_name

Note: INNER join is the same as join

For example, the following uses an equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:

The code is as follows Copy Code
SELECT *
From authors as a INNER JOIN publishers as P
On a.city=p.city


2. Unequal connection: The connection condition uses comparison operators other than the equals operator to compare the connected
The column value of the column. These operators include >, >=, <=, <,!>,!<, and <>.
3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected columns, but it uses the selected
An optional list indicates which columns are included in the query result collection and deletes duplicate columns from the attached table.
For example, delete the repeating columns (city and state) in the authors and Publishers tables in the select list:

The code is as follows Copy Code
SELECT A.*,p.pub_id,p.pub_name,p.country
From authors as a INNER JOIN publishers as P
On a.city=p.city


(ii) outer joins  

The left JOIN keyword returns all rows from the left-hand table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2).

Outer joins, which are returned to the query result collection to include not only rows that meet the join criteria, but also the left table (left
Outer or left connection), all rows of data in the right table (right outer or right) or two side tables (full outer joins).

A LEFT JOIN, which returns records that include all the records in the left table and the join fields in the right table are equal;
Right join, which returns records that include all the records in the right table and the join fields in the left table are equal;

Left JOIN keyword syntax

The code is as follows Copy Code
SELECT column_name (s)
From table_name1
Left JOIN table_name2
On Table_name1.column_name=table_name2.column_name

Note: In some databases, the left join is called the left OUTER join.

For example 1:

The code is as follows Copy Code
SELECT a.*,b.* from Luntan left JOIN usertable as B
On A.username=b.username


For example 2:

The code is as follows Copy Code
SELECT a.*,b.*
From city as a full OUTER JOIN user as B
On A.username=b.username

(iii) Cross-linking   
The cross join does not take a WHERE clause, which returns the Cartesian product of all data rows of the connected two tables, and returns to the
The number of rows in the result set equals the number of data rows in the first table that match the query criteria, multiplied by the second table.
The number of data rows to which the criteria are consulted. For example, there are 6 categories of books in the titles table, and there are 8 publishers in the publishers table.
The number of records retrieved by a column cross join is equal to the 6*8=48 line.
For example:

The code is as follows Copy Code
SELECT Type,pub_name
From titles CROSS JOIN Publishers
ORDER BY Type

SQL join-Using Join
In addition to the above method, we can also use keyword JOIN to get data from two tables.

If we want to list everyone's order, we can use the following SELECT statement:

The code is as follows Copy Code
SELECT Persons.lastname, Persons.firstname, Orders.orderno
From Persons
INNER JOIN Orders
On persons.id_p = orders.id_p
ORDER BY Persons.lastname

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.