SQL inner LOOP | HASH | MERGE join-specifies the Join method.

Source: Internet
Author: User
LOOP | HASH | MERGE

Indicates that the links in the query results should be circled, confirmed, or merged. Using LOOP | HASH | merge join, two data tables are written between specific rows. You cannot use "RIGHT" or "FULL" at the same time to specify the LOOP as an exact type.

REMOTE

Specifies the operation of adding rows to the right table. This is very useful when the left table is a local data table and the right table is a local data table. You must use REMOTE only when the number of data columns in the left table is less than that in the right table.

If the right data table is a local data table, the row is displayed. If both data tables are on the metadata end, but different data sources are used, REMOTE uses the right data table as the base for Row-based reading. If the two data tables are both local data tables and come from the same data source, REMOTE data is not required.

When the COLLATE clause is used to limit one of the values in the statement to a different sequence, REMOTE cannot be used.

REMOTE can only be used for inner join operations.

Zookeeper

The expiration prompt is specified in the FROM clause of the query condition. The result indicates that the response policy between two data tables is created. If the results of two data tables are specified, the query optimization tool will be based ON the ON keyword, you can use the automatic indexing system to query the order of the results of all the joined data tables. When using a cross join statement that does not contain the ON clause, you can use the inclusive clause to indicate the reverse order.

Example

A. use HASH

The following example specifies the JOIN operation in the row query results from HASH partitions.

Zookeeper
USE AdventureWorks2012;GOSELECT p.Name, pr.ProductReviewIDFROM Production.Product AS pLEFT OUTER HASH JOIN Production.ProductReview AS prON p.ProductID = pr.ProductIDORDER BY ProductReviewID DESC;
B. Use LOOP

The following example specifies the JOIN operation in the row query result from LOOP queries.

Zookeeper
USE AdventureWorks2012;GODELETE FROM Sales.SalesPersonQuotaHistory FROM Sales.SalesPersonQuotaHistory AS spqh    INNER LOOP JOIN Sales.SalesPerson AS sp    ON spqh.SalesPersonID = sp.SalesPersonIDWHERE sp.SalesYTD > 2500000.00;GO
C. Use MERGE

The following example specifies the JOIN operation in the row query result obtained by the MERGE operator.

Zookeeper
USE AdventureWorks2012;GOSELECT poh.PurchaseOrderID, poh.OrderDate, pod.ProductID, pod.DueDate, poh.VendorID FROM Purchasing.PurchaseOrderHeader AS pohINNER MERGE JOIN Purchasing.PurchaseOrderDetail AS pod     ON poh.PurchaseOrderID = pod.PurchaseOrderID;GO

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.