sql - इनर ज्वाइन पर क्रॉस एप्लाई कब लगाना चाहिए?

खैर मुझे यकीन नहीं है कि यह क्रॉस लागू बनाम इनर जॉइन का उपयोग करने के कारण के रूप में योग्यता प्राप्त करता है, लेकिन क्रॉस एप्लिकेशन का उपयोग करके फोरम पोस्ट में इस प्रश्न का उत्तर दिया गया था, इसलिए मुझे यकीन नहीं है कि इनर जॉइन का उपयोग करके एक समान विधि है या नहीं:

Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)

के रूप में BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp

समाप्त

मान लें कि आपके पास दो टेबल हैं।

मास्टर टेबल

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

विवरण तालिका

x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x

ऐसी कई स्थितियां हैं जहां हमें CROSS APPLY साथ INNER JOIN को प्रतिस्थापित करने की आवश्यकता है।

1. TOP n परिणामों के आधार पर दो टेबल में शामिल हों

गौर करें कि हमें Master से Id और Name चुनने की आवश्यकता है और Details table से प्रत्येक Id लिए अंतिम दो तिथियां हैं।

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

• एसक्यूएल फिडल

उपर्युक्त क्वेरी निम्नलिखित परिणाम उत्पन्न करती है।

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x

देखें, पिछले दो तिथियों के Id साथ पिछले दो तिथियों के लिए यह परिणाम उत्पन्न हुए और फिर इन रिकॉर्ड्स में केवल Id पर बाहरी क्वेरी में शामिल हो गए, जो गलत है। इसे पूरा करने के लिए, हमें CROSS APPLY का उपयोग करने की आवश्यकता है।

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

• एसक्यूएल फिडल

और निम्नलिखित परिणाम बनाता है।

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x

यहां देखिए यह कैसे काम करता है। CROSS APPLY अंदर की क्वेरी बाहरी तालिका का संदर्भ दे सकती है, जहां INNER JOIN ऐसा नहीं कर सकता है (यह संकलन त्रुटि फेंकता है)। पिछली दो तिथियों को WHERE M.ID=D.ID , CROSS APPLY अंदर शामिल होना यानी, WHERE M.ID=D.ID ।

2. जब हमें कार्यों का उपयोग करके INNER JOIN कार्यक्षमता की आवश्यकता होती है।

CROSS APPLY को INNER JOIN साथ प्रतिस्थापन के रूप में उपयोग किया जा सकता है जब हमें Master टेबल और function से परिणाम प्राप्त करने की आवश्यकता होती है।

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

और यहाँ समारोह है

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE [email protected]
)

• एसक्यूएल फिडल

जिसने निम्नलिखित परिणाम उत्पन्न किए

x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x

क्रॉस आवेदन के अतिरिक्त लाभ

APPLY UNPIVOT प्रतिस्थापन के रूप में इस्तेमाल किया जा सकता है। या तो CROSS APPLY या OUTER APPLY का उपयोग यहां किया जा सकता है, जो विनिमयशील हैं।

MYTABLE कि आपके पास निम्न तालिका है ( MYTABLE नामित)।

x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x

क्वेरी नीचे है।

SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

• एसक्यूएल फिडल

जो आपको परिणाम लाता है

  x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x

क्रॉस लागू का उपयोग subquery के स्थान पर करने के लिए किया जा सकता है जहां आपको सबक्वायरी के कॉलम की आवश्यकता होती है

सबक्वेरी

select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')

यहां मैं क्रॉस लागू करने का उपयोग करके कंपनी टेबल के कॉलम का चयन नहीं कर पाऊंगा

select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

यहां एक उदाहरण दिया गया है जब क्रॉस आवेदन प्रदर्शन के साथ एक बड़ा अंतर बनाता है:

क्रॉस का उपयोग करने के लिए स्थिति के आधार पर जुड़ने के लिए आवेदन करें

ध्यान दें कि आंतरिक जोड़ों को बदलने के अलावा आप स्केलर यूडीएफ को शामिल करने के लिए प्रदर्शन जुर्माना के भुगतान के बिना छंटनी तिथियों जैसे कोड का पुन: उपयोग कर सकते हैं, उदाहरण के लिए: इनलाइन यूडीएफ के साथ महीने के तीसरे बुधवार की गणना करना

cross apply कभी-कभी आपको उन चीजों को करने में सक्षम बनाता है जिन्हें आप inner join साथ नहीं कर सकते हैं।

उदाहरण (एक वाक्यविन्यास त्रुटि):

select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id

यह एक वाक्यविन्यास त्रुटि है , क्योंकि, जब inner join साथ प्रयोग किया जाता है, तो तालिका कार्य केवल पैरामीटर के रूप में चर या स्थिरांक ले सकते हैं। (यानी, तालिका फ़ंक्शन पैरामीटर किसी अन्य तालिका के कॉलम पर निर्भर नहीं हो सकता है।)

तथापि:

select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id

यह कानूनी है।

संपादित करें: या वैकल्पिक रूप से, छोटे वाक्यविन्यास: (एरिक द्वारा)

select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id

संपादित करें:

नोट: इनफॉर्मिक्स 12.10 xC2 + में लेटरल व्युत्पन्न टेबल्स और पोस्टग्रेस्क्ल (9.3+) में लेटरल सबक्विरीज़ हैं जिनका उपयोग इसी तरह के प्रभाव में किया जा सकता है।

यह शायद एक पुराना सवाल है, लेकिन मुझे अभी भी तर्क के पुन: उपयोग को सरल बनाने और परिणामों के लिए "श्रृंखला" तंत्र प्रदान करने के लिए क्रॉस आवेदन की शक्ति पसंद है।

मैंने नीचे एक एसक्यूएल फिल्ड प्रदान किया है जो कि एक सरल उदाहरण दिखाता है कि आप क्रॉस एप्पल का उपयोग अपने डेटा सेट पर जटिल लॉजिकल ऑपरेशंस करने के लिए कैसे कर सकते हैं बिना किसी गड़बड़ के। यहां अधिक जटिल गणनाओं से बाहर निकलना मुश्किल नहीं है।

http://sqlfiddle.com/#!3/23862/2

क्रॉस एक एक्सएमएल फ़ील्ड के साथ अच्छी तरह से काम करता है। यदि आप अन्य फ़ील्ड के साथ संयोजन में नोड मानों का चयन करना चाहते हैं।

उदाहरण के लिए, यदि आपके पास कुछ एक्सएमएल युक्त टेबल है

<root>
    <subnode1>
       <some_node value="1" />
       <some_node value="2" />
       <some_node value="3" />
       <some_node value="4" />
    </subnode1>
</root>

क्वेरी का प्रयोग करना

SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id

परिणाम लौटाएगा

xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY

मुझे लगता है कि यह पठनीयता होना चाहिए;)

क्रॉस आवेदन उन लोगों को बताने के लिए कुछ अद्वितीय होगा जो उन्हें बताते हैं कि एक यूडीएफ का उपयोग किया जा रहा है जो बाईं ओर तालिका से प्रत्येक पंक्ति पर लागू किया जाएगा।

संभोग, ऐसी अन्य सीमाएं हैं जहां एक क्रॉस आवेदन जॉइन से बेहतर उपयोग किया जाता है जो अन्य मित्रों ने ऊपर पोस्ट किया है।

यहां एक लेख है जो जॉइन पर उनके प्रदर्शन अंतर और उपयोग के साथ, सभी को समझाता है।

एसक्यूएल सर्वर क्रॉस आवेदन और संयुक्त आवेदन पर लागू आवेदन

जैसा कि इस आलेख में सुझाया गया है, उनके बीच सामान्य जुड़ने के संचालन (INNER और CROSS) के बीच कोई प्रदर्शन अंतर नहीं है।

जब आप इस तरह की कोई क्वेरी करना चाहते हैं तो उपयोग अंतर आता है:

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO 
SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)

यही है, जब आपको कार्य से संबंधित होना है। यह इनर जॉइन का उपयोग करके नहीं किया जा सकता है, जो आपको त्रुटि देगा "बहु-भाग पहचानकर्ता" डी। डिपार्टमेंट आईडी "बाध्य नहीं हो सका।" यहां मूल्य को फ़ंक्शन पर पास किया जाता है क्योंकि प्रत्येक पंक्ति पढ़ी जाती है। मुझे अच्छा लगता है। :)

declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 100000)

 insert @local values(cast(@p as varchar(max)))
 select DATALENGTH(col) as collen, col from @local

result collen > 8000, length col value is more than 8000 chars