SQL table connection, SQL table

SQL table connection, SQL table
Background
In the last self-study subject database system principles, I have gained some knowledge about database table connections. Recently, I used database table connections, let's take a look at it again.
Graph export Summary
First, use a mind map to summarize the content of SQL table join:


After having a rough understanding of SQL table join, let's take a further look at it through a small example. First, I create two tables:



External Connection
External connections include left outer connections, right outer connections, and full outer connections.
Left Outer Join
SQL statement: select * from table1 left join table2 on table1.id = table2.id


Outer right connection
SQL statement: select * from table1 right join table2 on table1.id = table2.id


Complete connection
SQL statement: select * from table1 full join table2 on table1.id = table2.id



Internal Connection
SQL statement: select table1.id, table2.score from table1 inner join table2 on table1.id = table2.id






Cross join
SQL statement: select * from table1 cross join table2 on table1.id = table2.id





In fact, learning is like this. At the same time, through review, each time there will be different gains, a little deeper understanding.



SQL full join multi-table join

Select t1.name, t1.x1, t2, x2, t3.x3
From table1 t1
Full join table t2 on t1.name = t2.name
Full join table3 t3 on t1.name = t3.name

This is enough

An SQL connection table problem

Because there are many tables, this query is complicated. In fact, the difficulty lies in how to return the maximum number of rows when the number of rows of the same ID in the two tables is different, and the one-to-one correspondence of each row cannot be repeated.
Take table B and Table C as examples.
First, use the row_number () function to generate an Auxiliary Column seq (sequence number) for table B and Table C respectively ). The result is as follows:
Select *, row_number () over (partition by Id order by Comment, Time) as Seq from B
Id Comment Time Seq
1 c1 t1 1
1 c2 t2 2
1 c3 t3 3
4 c4 t4 1
4 c5 t5 2
4 c6 t6 3
7 c7 t7 1
-- Select *, row_number () over (partition by Id order by Year, Nick) as Seq from C
Id Year Nick Seq
1 y1 n1 1
7 y2 n2 1
Next, use Full Join to Join the two tables.
Select isnull (TB. Id TC. Id) as Id, Comment, Time, Year, Nick
From
(Select *, row_number () over (partition by Id order by Comment, Time) as Seq from B) as TB
FULL JOIN
(Select *, row_number () over (partition by Id order by Year, Nick) as Seq from C) as TC
On TB. Id = TC. Id and TB. Seq = TC. Seq
The result should be as follows:
Id Comment Time Year Nick
1 c1 t1 y1 n1
1 c2 t2 null
1 c3 t3 null
4 c4 t4 null
4 c5 t5 null
4 c6 t6 null
7 c7 t7 y2 n2
Then, the full join table D is returned in the same way.
Finally, the results of the first few steps of Table A LEFT JOIN