Creating a more efficient association

Question

Here is my request, it is quite simple:

SELECT
    INVOICE_ITEMS.II_IVNUM, INVOICE_ITEMS.IIQSHP
FROM
    INVOICE_ITEMS
LEFT JOIN
    INVOICES
ON 
    INVOICES.INNUM = INVOICE_ITEMS.II_INNUM
WHERE
    INVOICES.IN_DATE
BETWEEN
    '2010-08-29' AND '2010-08-30'
;

I have very limited knowledge of SQL, but I'm trying to understand some concepts, such as subqueries and the like. I'm not looking for a redesign of this code, but rather an explanation of why it is so slow (600+ seconds in my test database) and how I can do it faster.

In my opinion, the left join creates a virtual table and populates it with each sequence of results from the join, which means that it processes each row. How can I stop the query from reading the table completely and just find the sentence WHERE/BETWEENand then create a virtual table after that (if possible)?

How is my logic? Are there constantly recommended resources to get SQL ninja status?

: . ODBC , OMNIS. , , , MSSQL.

Answer 1

, i.INNUM, ii.INNUM i.IN_DATE. LEFT JOIN INNER JOIN WHERE, :

SELECT ii.II_IVNUM, ii.IIQSHP 
FROM INVOICE_ITEMS ii
INNER JOIN INVOICES i ON i.INNUM = ii.II_INNUM 
WHERE i.IN_DATE BETWEEN '2010-08-29' AND '2010-08-30' 

, , , INVOICE_ITEMS (- LEFT JOIN), , INVOICE , WHERE , . INNER JOIN, , WHERE INVOICES , INVOICE_ITEMS.

Answer 2

SInce - , , , . In_date INVOICE_ITEMS.II_INNUM? PK Fk, INVOICES.INNUM , FK .

Answer 3

, , .

INVOICES.INNUM INVOICE_ITEMS.II_INNUM?

SQL - "scan" - .

, - , . (, , "... " ), .

- (, - ) (, - , ).

- , SQL. - , : , .

Answer 4

, ... , , INVOICES.INNUM = INVOICE_ITEMS.II_INNUM? , , /...

Answer 5

" " - , .

, , . :

INVOICE_ITEMS.II_INNUM
INVOICES.IN_DATE

INVOICES.INNUM, , .

Also, do not use the left join here. If the foreign key is between INVOICE_ITEMS.II_INNUMand INVOICES.INNUM(and is INVOICE_ITEMS.II_INNUMnot null), you will never encounter an entry in INVOICE_ITEMSwhich will not match an entry in INVOICES. Even if it were, your condition WHEREuses the value from INVOICES, so you will exclude any inconsistent lines anyway. Just use regular JOIN.