Join conditions breakdown

Join conditions breakdown.txt

given

STATEMENTS:
ID | REVIEW_ID | BANK_STATEMENT_DOCUMENT_ID
---|-----------|---------------------------
6  | 8         | 138
7  | 9         | 137

DOCUMENTS
d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
------|---------------------|------------------
  134 |                     |
  135 |                     |
  136 |                     |
  137 | Review              | 9
  138 | Review              | 8

====================================================================================================

FIRST QUERY:

select count(*)
from
statements s
left join documents d ON d.id = s.bank_statement_document_id AND d.documentable_type = 'Review' AND d.documentable_id = 8


`from statement LEFT JOIN` results in row 6 and 7 (ie. all rows), let's call that statement_rows
s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID
-----|-------------|-----------------------------
6    | 8           | 138
7    | 9           | 137

for each in statement_rows
    row 6:
        get all rows in documents where 
            d.id = s.bank_statement_document_id AND d.documentable_type = 'Review' AND d.documentable_id = 8
            in other words:
            d.id = 138 and d.documentable_type = 'Review' AND d.documentable_id = 8
            results in 1 row:
                s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
                -----|-------------|----------------------------- | ------|---------------------|------------------
                6    | 8           | 138                          |   138 | Review              | 8

    row 7:
        get all rows in documents where 
            d.id = s.bank_statement_document_id AND d.documentable_type = 'Review' AND d.documentable_id = 8
            in other words:
            d.id = 137 and d.documentable_type = 'Review' AND d.documentable_id = 8
            results in 1 row:
                s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
                -----|-------------|----------------------------- | ------|---------------------|------------------
                7    | 9           | 137                          |  NULL | NULL                | NULL
combine it:

s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
-----|-------------|----------------------------- | ------|---------------------|------------------
6    | 8           | 138                          |   138 | Review              | 8
7    | 9           | 137                          |  NULL | NULL                | NULL

COUNT(*) = count all rows in result => 2


====================================================================================================
Second query:

select count(*)
from statements s
left join documents d ON d.id = s.bank_statement_document_id AND d.documentable_type = 'Review'
WHERE d.documentable_id = 8


`from statement LEFT JOIN` also results in row 6 and 7 (ie. all rows), let's call that statement_rows
s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID
-----|-------------|-----------------------------
6    | 8           | 138
7    | 9           | 137


for each in statement_rows
    row 6:
        get all rows in documents where 
            d.id = s.bank_statement_document_id AND d.documentable_type = 'Review'
            in other words:
            d.id = 138 and d.documentable_type = 'Review'
            results in 1 row:
                s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
                -----|-------------|----------------------------- | ------|---------------------|------------------
                6    | 8           | 138                          |   138 | Review              | 8

    row 7:
        get all rows in documents where 
            d.id = s.bank_statement_document_id AND d.documentable_type = 'Review'
            in other words:
            d.id = 137 and d.documentable_type = 'Review'
            results in 1 row:
                s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
                -----|-------------|----------------------------- | ------|---------------------|------------------
                7    | 9           | 137                          |  NULL | NULL                | NULL
combine it:

s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
-----|-------------|----------------------------- | ------|---------------------|------------------
6    | 8           | 138                          |   138 | Review              | 8
7    | 9           | 137                          |  NULL | NULL                | NULL

Then apply the WHERE d.documentable_id = 8 clause:

s.ID | s.REVIEW_ID | s.BANK_STATEMENT_DOCUMENT_ID | d.ID  | d.DOCUMENTABLE_TYPE | d.DOCUMENTABLE_ID
-----|-------------|----------------------------- | ------|---------------------|------------------
6    | 8           | 138                          |   138 | Review              | 8

Then COUNT(*) => 1