Welcome to “Algorithms and the Beauty of Programming” ↑ pay attention to us!
This article was first published on the wechat official account “Beauty of Algorithms and Programming”. Please follow us and learn more about this series of articles in time.
1 Problem Description
In the database learning, we know that the database involves multiple table queries need to use different connection queries, SQL will be connected into: internal connection, external connection, natural connection, cross connection; Among them, inner connection and outer connection are more commonly used. Inner join: Fetch data from the left table to match all records in the right table: The matching result is retained only if the result of a condition is the same in the left table and the right table. Otherwise, it is not retained. In relational database management system, the relationship between data does not need to be determined when the table is established, and often all information of an entity is stored in one table. When data is retrieved, join operations are used to query information about different entities stored in multiple tables. The join operation gives us a lot of flexibility to add new data types. Then query through the join. Next I will briefly talk about my understanding of several database join query methods.
2 Problem Analysis
Multiple table queries can be implemented with join operators. Connection is the main characteristic of relational database model, which is also a mark that distinguishes it from other types of database management system.
Join queries can be divided into three types: inner join, outer join, and cross join. Inner joins use comparison operators to compare columns of data between tables and list rows in those tables that match join conditions. According to the different comparison methods used, the internal connection is divided into equivalent connection, natural connection and unequal connection. The outer connection is divided into three kinds: left outer connection, right outer connection, and whole outer connection. Unlike an inner join, an outer join lists not only the rows that match the join criteria, but all the rows in the left table (when left outer join), the right table (when right outer join), or both tables (when full outer join) that match the search criteria.
3 Solution
Inner join or join
An inner join is an equivalent join that uses operators such as “=, >, <, <>” to match rows in two tables based on the values of the columns shared by each table
Query statement:
Left join (left join or left outer join)
A left join is also called a left-out join. The result set of a query includes all rows of the left table and matched rows of the right table in the SQL statement. If a row in the left table has no matching row in the right table, it is represented by a null value
Query statement:
Right join or right outer join
The result set of the query includes all rows from the right table in the SQL statement and matched rows from the left table. If a row in the right table has no matching row in the left table, it is represented by a null value
Query statement:
Full join or full outer join
The result set of the query includes all rows of the left and right tables in the SQL statement. If a row has no matching row in another table, it is represented by a null value.
Query statement:
4 summarizes
INNER JOIN: Returns rows if at least one match exists in each of the queried tables
LEFT JOIN: Returns all rows from the LEFT table even if there is no match in the right table
RIGHT JOIN: Returns all rows from the RIGHT table even if there is no match in the left table
FULL JOIN: Returns rows as long as there is a match in one of the tables
UNION: Combines multiple result sets of SELECT statements
More interesting articles:
Tips: Click on the lower right corner of the page “Write a message” to comment, looking forward to your participation! Looking forward to your forwarding