SQL query learning and practice accumulation

SQL is a very basic and important knowledge in a relational database, although the backend Development class library like Laravel provides an ORM abstract data class that encapsulates a subset of simple SQL queries, so many times we don't need to relate the specifics of SQL can develop their own backend applications very quickly, but when it comes to relatively complex relationships we still have to resort to SQL. Benbow as the year of the rooster, a beginning, and constantly record the accumulation of my own in the SQL learning to feel important points drop, learning resources, memo, but also hope to the people to help

SQL Subselect and correlated subquery

Subquery is one of the SELECT statement that is submerged in parentheses in another SQL statement. In most cases, it is very easy to fully implement the function of a sub-select statement (or vice versa) with a join statement. But many times, subquery is more understandable and logical than the Join method. For example, like in, the use of any of these keywords often makes statements easier to understand and easily decomposed. As an example, let's look at the query statement for the following question:

"List all guest lists in NJ State"

Select Name from Customers where CustomerID = No ( select CustomerID from addressbook where state = ' NJ')

In this example, the bold part of the parentheses is a subquery.

This subquery is called "non-correlated" by US subquery, The reason is that you can execute this SELECT statement individually to get a logical and correct result set. In this example, the isolated subquery can produce a list of customers from the NJ state.

However, there is a correlated subquery relative to this non-correlated, which contains a reference to the value of outer query and cannot be executed separately from the outer query.

Let's give a typical query example:

SELECT * from t1 where column1 = no (select Column1 from t2 where t2.column2 = T1.column2)

Note that in this example, although the T1 table is not indicated in the subquery FROM clause but is referenced by the WHERE clause in subquery, the T1 table exists only in the outer query statement, if you execute the isolated subquery directly, You will get an error because you cannot find the T1 table.

SQL query learning and practice accumulation