SQL Outer Join left on right on

SQL Outer Join left on right on

SQL Outer Join left on right on

I. join)

Through join, you can retrieve data from two or more tables based on the logical relationship between tables. Join indicates how to use data in one table to select rows in another table.

The join condition defines the join mode of two tables in the query using the following method:

Specify the columns to be joined in each table. A typical join condition specifies a foreign key in one table and a key associated with it in another table. Specifies the logical operators (=, <>, and so on) used to compare the values of each column ).

 

Ii. Connection Type

When a table is joined, the created join type affects the rows in the result set. Join type:

 

 

Iii. Inner join

An inner join is also called a natural join. It is a common method for combining two tables. The natural connection compares the columns in the two tables and combines the rows that meet the connection conditions in the two tables as a result. There are two forms of natural concatenation syntax.

Syntax 1:

SELECT column FROM table 1 [insert] JION table 2 ON table 1. Column = TABLE 2. Column

Syntax 2:

SELECT column FROM table 1, Table 2 WHERE Table 1. Column = TABLE 2. Column

[Example] query the book number, title, author number, and author name in the titles, authors, and titleauthor tables.

Select titles. title_id, title, authors. au_id, au_lname from titles join titleauthor on titles. title_id = titleauthor. title_id join authors on authors. au_id = titleauthor. au_id

 

4. Outer join)

In a natural join, only matching rows in two tables can appear in the result set. In Outer Join, only one table can be restricted, but other tables are not limited (that is, all rows appear in the result set ).

External connections include left outer connections, right outer connections, and full outer connections. The left Outer Join Operation does not limit the table on the left of the connection condition. The right outer join operation does not limit the table on the right. The full outer join operation does not limit both tables, all rows in the two tables are included in the result set.

 

V. Outer join syntax

Syntax for left outer join: SELECT column FROM table 1 LEFT [OUTER] JOIN table 2 ON table 1. Column 1 = TABLE 2. Column 2

The right outer join syntax is: SELECT select_list FROM table 1 RIGHT [OUTER] JOIN table 2 ON table 1. Column 1 = TABLE 2. Column 2

The syntax of the full outer join (full outer join) is: SELECT select_list FROM table 1 FULL [OUTER] JOIN table 2 ON table 1. Column 1 = TABLE 2. Column 2

 

6. left Outer Join

Includes all rows in the first named table (left table, which appears on the far left of the JOIN clause. Does not include unmatched rows in the right table.

[Example 35] the following SQL statement shows that the left Outer Join between the titles table and the publishers table includes all the titles, and even the titles without Publisher Information: Use pubs SELECT titles. title_id, titles. title, publishers. pub_name FROM titles left outer join publishers ON titles. pub_id = publishers. pub_id

 

 

7. Right Outer Join

Includes all rows in the second named table ("right" table that appears on the rightmost side of the JOIN clause. Does not include unmatched rows in the left table.

[Example 36] the right outer join between the titles and publishers tables includes all publishers, even those who have no titles in the titles table.

SELECT titles. title_id, titles. title, publishers. pub_name FROM titles right outer join publishers ON titles. pub_id = publishers. pub_id

 

 

8. Complete External Connection

Includes all rows in all join tables, whether or not they match.

[Example 37] The Complete External Connection Between the titles table and the publishers table shows all the titles and publishers, and even the names and publishers of those tables that do not have matching values.

SELECT titles. title_id, titles. title, publishers. pub_name FROM titles full outer join publishers ON titles. pub_id = publishers. pub_id

 

 

IX. Cross join

A cross join without a WHERE clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set.

[Example 39] Read the following procedure:

USE pubs

SELECT au_fname, au_lname, pub_name FROM authors cross join publishers order by au_lname DESC

Note: The result set contains 184 rows (authors has 23 rows, publishers has 8 rows, and 23 multiplied by 8 equals to 184 ). However, if you add a WHERE clause, the cross join function is the same as that of the inner join.

We have seen the left join, also known as inner join ). In this case, the data will be selected only when both tables have the same value. What should we do if we want to list every piece of information in a table, whether its value is in another table or not? At this time, we need to use the SQL OUTER JOIN command.

The external connection syntax varies depending on the database tutorial. For example, in Oracle, a "(+)" is added to the table in which all data is to be selected in the WHERE clause to indicate that all data in this table is required.

Suppose we have two tables:

Store_Information table
Store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

 
Geography table region_name store_name
East Boston
East New York
West Los Angeles
West San Diego
 

We need to know the turnover of each store. If we use a normal connection, we will miss the store 'New York 'because it does not exist in the table Store_Information. Therefore, in this case, we need to use an external connection to concatenate the two tables:

SELECT A1.store _ name, SUM (A2.Sales) SALES
FROM Georgraphy A1, Store_Information A2
WHERE A1.store _ name = A2.store _ name (+)
Group by A1.store _ name

Here we use the Oracle External Connection syntax.

Result:

Store_name SALES
Boston $700
New York
Los Angeles $1800
San Diego $250

Note: When the second table does not have relative data, the SQL returns a NULL value. In this example, 'New York 'does not exist in the Store_Information table, so its "SALES" column is NULL.

SQL union

 

The purpose of the UNION command is to combine the results of two SQL statements. From this perspective, UNION and JOIN are somewhat similar, because both commands can retrieve data from multiple tables. One Limit of UNION is that the columns generated by two SQL statements must be of the same data type. In addition, when we use the UNION command, we only see different data values (similar to select distinct ).

The UNION syntax is as follows:

[SQL statement 1]
UNION
[SQL statement 2]

Suppose we have two tables,

Store_Information table
Store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

 
Internet Sales table Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
January 12-1999 $750
 

We need to find out all the days with a turnover. To achieve this goal, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

Result:

Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999

It is worth noting that if we use "select distinct Date" in any SQL statement (or both sentences), we will get the same result.

The purpose of the union all command is to combine the results of two SQL statements. The difference between union all and UNION is that union all lists each qualified data, regardless of whether the data value is repeated.

The syntax of union all is as follows:

[SQL statement 1]
UNION ALL
[SQL statement 2]

We use the same example on the previous page to show the differences between union all and UNION. Let's assume that we have the following two tables,

Store_Information table
Store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

 
Internet Sales table Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
January 12-1999 $750
 

However, we need to find out the days when there are store turnover and network turnover. To achieve this goal, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

Result:

Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Jan-07-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999