Hash Join require Full Table Scan

Question

So, I want to know if to make a Hash Join between two tables is necessary to make a full table scan on the collumns?

If i want to join COL1 wiht COL2, and COL1 is smaller, the It makes a full scan in COL1 creating a Hashmap then makes a full scan in COL2 using the sabe hash function.

Is this correct?

Answer 1

every database could have it's own actual implementation of aHash Join. however I'd say the likely method is similar to this

The Hash Join algorithm builds an in-memory hash table of the smaller of its two inputs, and then reads the larger input and probes the in-memory hash table to find matches, which are written to a work table. If the smaller input does not fit into memory, the hash join operator partitions both inputs into smaller work tables. These smaller work tables are processed recursively until the smaller input fits into memory.

As for the question: is necessary to make a full table scan on the collumns

I'd say no, which also depends on the database and how well it can optimize things. If there are sufficient conditions in the query to limit the rows in either table then, it will pull those rows out before it uses the hash merge algorithm.

When it builds the in-memory hash table of the smaller of its two inputs, it will pull those rows out of the table using the best method, which isn't necessarily a table scan. If you have no conditions in the query to reduce the rows on this table, then it would do a table scan.

When then reads the larger input and probes the in-memory hash table to find matches, it will pull those rows out using the best method also, which isn't necessarily a table scan.

if your query is:

SELECT
    *
    FROM BigTable
        INNER JOIN LittleTable ON BigTable.Col=LittleTable.Col

and a hash join is used, it will most likely create a hash table in memory from the LittleTable by doing a table scan, and then table scan BigTable checking against those hash keys.

if your query is:

SELECT
    *
    FROM BigTable
        INNER JOIN LittleTable ON BigTable.Col=LittleTable.Col
    WHERE LittleTable.Col2 >'2010/01/01' AND LittleTable.Col2<'2010/01/31'

and a hash join is used, it will most likely create a hash table in memory from the LittleTable but not using a table scan (if there is an index to use), and then table scan BigTable checking against those hash keys. Add more filter to change the remove the table scan on BigTable.