Preface

Connection query has recently been used in the project , Join query is the most important query in relational data , Including internal connections 、 External connection, etc . Multiple table queries can be implemented through join operators . Let's review .

Text

There are three main types of join queries ： Internal connection 、 External connection 、 Cross connect .

Internal connection

Use the comparison operator （ Include =、>、<、<>、>=、<=、!>  and !<） Compare tables , Query the data that matches the join condition . Depending on the comparison method used , Inner connection is divided into equivalent connection 、 Natural connection and self connection .

keyword  INNER JOIN

Equivalent connection / Equal connection

Use ”=” Relationships are queries that join tables , All the columns in the connected table are listed in the query result , Include the repeating Columns

SELECT
    PM_ACT_JOB_RLS.*, PM_ACT_RLS.*
FROM
    PM_ACT_JOB_RLS
INNER JOIN PM_ACT_RLS ON PM_ACT_JOB_RLS.RlsPK = PM_ACT_RLS.RlsPK

Natural join

Remove duplicate columns from the equivalent join , Form a connection

SELECT
    PM_ACT_JOB_RLS.JobPK,
    PM_ACT_RLS.RlsPK,
    RlsName
FROM
    PM_ACT_JOB_RLS
INNER JOIN PM_ACT_RLS ON PM_ACT_JOB_RLS.RlsPK = PM_ACT_RLS.RlsPK

Self join

If in a join query , The two tables designed are the same table , This kind of query is called self join query .

--c1、c2 Logically, there are two tables , Physically, it's a table 
SELECT
    c1.CategoryID,
    c1.CategoryName
FROM
    [dbo].[Category] c1
INNER JOIN [dbo].[Category] c2 ON c1.[CategoryID] = c2.[ParentID]

External connection

The inner connection only returns the data rows that meet the connection conditions , The external connection lists not only the rows that match the connection conditions , Instead, list the left table （ Left outer connection ）、 Right table （ Right outer connection ） Or two tables （ When fully externally connected ） All data rows that meet the search criteria in the . External connection is divided into left external connection 、 Right connection 、 There are three types of total external connection .

The left outer join

Returns all rows in the left table , If the row in the left table does not match the row in the right table , The selected fields in the right table of the associated result set are NULL.

keyword  LEFT [OUTER] JOIN

-- The left outer join --1679 Data
SELECT
me.*,
mo.*
FROM
MM_LOTS_EXT AS me
LEFT OUTER JOIN MM_LOT_OPERATIONS AS mo ON me.LotID = mo.SerialNumber

Right connection

Returns all the rows in the right table , If the row in the right table does not match the row in the left table , The relevant fields in the left table return NULL value .

keyword  RIGHT [OUTER] JOIN

-- Right connection   --209 Data 
SELECT
    me.*,
    mo.*
FROM
    MM_LOTS_EXT AS me
RIGHT OUTER JOIN MM_LOT_OPERATIONS AS mo ON me.LotID = mo.SerialNumber

Full outer join / Complete external connection

Return all recorded data in two connections , Is the union of left outer connection and right outer connection .

keyword  FULL [OUTER] JOIN

-- Full outer join   --1816 Data  
SELECT
    me.*,
    mo.*
FROM
    MM_LOTS_EXT AS me
FULL OUTER JOIN MM_LOT_OPERATIONS AS mo ON me.LotID = mo.SerialNumber

Cross connect / The cartesian product

Cartesian product of two tables , The number of rows in the result set is the product of the number of rows in the two tables .

keyword  CROSS JOIN

-- The cartesian product  (cross join After the condition can only be used where, Out-of-service on)  --45 Data （9*5）
SELECT
    *
FROM
    PM_ACT_JOB_RLS
CROSS JOIN PM_ACT_RLS

Be careful ： with where Conditional clause , It is often a data table that is the product of the number of rows in two tables , Then according to where Conditions choose from .

When the data volume is larger than the price , Cartesian operation will consume the performance of database

summary

Join query is an important symbol in relational database , This time I will review several operations of multi table query with you , Hope to help you ！