Improve Your App with SQL Joins – Shorter & Faster Queries Using Inner Join!

Summary

The video demonstrates how to simplify an email-verification query in an Express app using Drizzle ORM’s inner join. The presenter combines data from a verification-token table and a users table in a single query, replacing a longer approach.

Main Ideas

• A join retrieves related data from two or more tables in one query.
• An inner join returns rows only when the join condition matches in both tables.
• In this example, the verification-token table contains the user ID, token, and expiration time; the users table contains the user’s email.
• The query checks that the token matches, has not expired, and belongs to the email supplied in the verification link.
• Drizzle’s select query returns an array, so the controller must account for that when extracting the result.
• Testing revealed a missing or incorrectly passed email value and a query typo. The presenter corrected these and retested the verification flow.

Method Shown

1. Select the needed fields:
   • User ID and email from the users table.
   • Token and expiration time from the verification-token table.
2. Start the query from the verification-token table.
3. Add an inner join to the users table, matching the token record’s user ID with the users table’s ID.
4. Add conditions to verify that:
   • The token matches the token in the request.
   • The token has not expired.
   • The user’s email matches the email in the request.
5. Handle the selected result as an array in the controller, extracting the first result where appropriate.
6. Test the verification link, inspect errors, and correct issues such as missing request data or a query typo.

The takeaway is to use joins when related data from multiple tables is needed together. The video also briefly previews a possible future improvement: sending a verification email immediately after registration.

Speakers and Sources Featured

• Presenter/narrator: The video’s instructor, explaining and demonstrating the code.
• Referenced material: A previous video about SQL joins, including join types and related explanations.
• Demonstration sources: The app’s code, database tables, and email-verification flow shown during testing. No other speakers are heard.