Database Table connection

Summary of Oracle full connection
-- Create Test Data
Create Table A (ID number );
Create Table B (ID number );
Insert into a values (1 );
Insert into a values (2 );
Insert into a values (3 );
Insert into B values (1 );
Insert into B values (2 );
Insert into B values (4 );
Commit;

-- Left:
-- Common methods for mainstream Databases
Select * from a left join B on A. ID = B. ID;
-- Methods unique to Oracle
Select * from a, B where a. ID = B. ID (+ );

ID
--------------------
1 1
2 2
3

-- Right:
-- Common methods for mainstream Databases
Select * from a right join B on A. ID = B. ID;
-- Methods unique to Oracle
Select * from a, B where a. ID (+) = B. ID;

ID
--------------------
1 1
2 2
4


-- Intranet
-- The connection in the common method of mainstream databases is the same as the connection in the WHERE clause.
Select * from a join B on A. ID = B. ID; (inner can be omitted)
-- Where Association
Select * from a, B where a. ID = B. ID;

ID
--------------------
1 1
2 2


-- All external
-- Common methods for mainstream Databases
Select * from a full join B on A. ID = B. ID;
-- Methods unique to Oracle
Select *
From a, B
Where a. ID = B. ID (+)
Union
Select *
From a, B
Where a. ID (+) = B. ID;

ID
--------------------
1 1
2 2
3
4


-- Complete, also called cross join or Cartesian Product
-- Common methods for mainstream Databases
Select * From A, B;
-- Or
Select * from a cross join B;

The on condition cannot be added for major cross join operations.

ID
--------------------
1 1
1 2
1 4
2 1
2 2
2 4
3 1
3 2
3 4


The connection is nothing more
-- The internal connection and where are the same
Inner join
-- Left Outer Join, return all the table on the left that meet the conditions
Left join
-- Right Outer Join, return all the qualified
Right join
-- Complete external connection, left external connection and right external connection
Full join
-- Crossover, also known as the flute product. Returns the combination of each row in the left table and all rows in the right table.
Cross join


-- Supplement:
-- Left Outer Join: returns all the table on the left that meet the conditions,
-- Note that there is no second plus sign, and data is filtered out directly, only matching records are displayed.
Select *
From a, B
Where a. ID = B. ID (+)
And B. ID = 2;

ID
--------------------
2 2


-- Left Outer Join, return all the table on the left that meet the conditions
-- Note that the second plus sign on where is used to modify the display of records in the right table. For example, if B. ID (+) = 2, 2 is displayed; otherwise, null is displayed.
Select *
From a, B
Where a. ID = B. ID (+)
And B. ID (+) = 2;

ID
--------------------
2 2
3
1

 

 

Note: The difference between on and where:

1. Left join and right join. Inner join conditions cannot be placed behind where and must be placed behind on.

2. Cross join executes Cartesian product, which is the same as separating table names using the bean number. Therefore, you must place the condition after where and not after on.

 

 

 

An example of a specific application

Select S. inareaid, O. stationid, O. routeid, count (*) as CNT
From tc_outlist as O right join tc_squdlog -- connect the two tables first

On O. stationid = tc_squdlog.staid and O. laneid = tc_squdlog.laneid and O. squadon = tc_squdlog.squadon -- filter condition for right join
, Mc_station as s -- then establish an internal connection with this table
Where o. findt = '20140901' and O. instaid = S. stationid and S. verid = O. ratever -- filter condition for inner join
Group by O. stationid, S. inareaid, O. routeid -- Group

 

-- Or write it like this
Select S. inareaid, O. stationid, O. routeid, count (*) as CNT
From tc_outlist as O right join tc_squdlog on O. stationid = tc_squdlog.staid and O. laneid = tc_squdlog.laneid and O. squadon = tc_squdlog.squadon
Join mc_station as s on O. instaid = S. stationid and S. verid = O. ratever
Where o. findt = '20140901'
Group by O. stationid, S. inareaid, O. routeid