SQL connection queries

10:37:10 | category:
technical article | Tag:
| font size large medium small subscription

 1. When two tables in natural join are connected through natural join, it is equivalent to an implicit WHERE clause. Compare the corresponding columns with the same name in two tables to see if they are equal. 2. Cross join (create Cartesian Product) generates the third table that returns the result set for the two tables through cross join. It is equivalent to a normal connection. 3. Inner join (inner join) Internal join is equivalent to normal cross join, but the format is different. Inner join has a Search Condition for the on clause (equivalent to where) following it, used to filter returned rows. 4. Select * from Ta Outer Join TB on (TA. c1 = TB. c1) Outer Join tells the DBMS to generate the result table, which not only has correlation (TA. c1 = TB. c1), and there are non-matched rows from any of the two source tables. 5. left Outer Join (left join) Right Outer Join (right join) Select * from Ta left Outer Join TB on (TA. c1 = TB. c1) Select * from Ta right Outer Join TB on (TA. c1 = TB. c1) left Outer Join (left join) tells DBMS to generate a result table that includes union rows and any unmatched rows, however, the unmatched rows are from the table on the left of the left Outer Join keyword in the from clause of the query. Right Outer Join (right join) is the opposite of left Outer Join (left join. 6. Full outer join (full join) Full outer join returns the tables that combine the left Outer Join and right Outer Join results.