DEV Community

Kelvin Wangonya
Kelvin Wangonya

Posted on • Originally published at wangonya.com

2

Be careful with join type typos

I noticed a typo in one of my sql queries today, but the funny thing is the query still worked fine. It looked something like this:

SELECT
    *
FROM
    table_1
LEFFT JOIN
    table_2
USING (a,b);
Enter fullscreen mode Exit fullscreen mode

At first I thought BigQuery was pretty smart and just assumed I meant LEFT instead. Maybe it's a common typo and there's an alias for it. So I got curious and tried out this:

SELECT
    *
FROM
    table_1
LEFFFFTFT JOIN
    table_2
USING (a,b);
Enter fullscreen mode Exit fullscreen mode

No error. That can't be right.

That's when it hit me. This query isn't doing a LEFT JOIN. It's using the incorrectly spelled name as an alias for table_1 and doing an INNER JOIN instead. Basically this:

SELECT
    *
FROM
    table_1 AS LEFFT
JOIN
    table_2
ON LEFFT.a = table_2.a AND LEFFT.b = table_2.b
Enter fullscreen mode Exit fullscreen mode

So the query works fine but the results would be incorrect.

This is one merit of syntax highlighting I've never really thought about until now. The only reason I noticed this quickly was by seeing LEFFT wasn't highlighted as I expected. The results of the query looked fine at first glance so this would have definitely bit me somewhere down the line if it got to production.

Image of AssemblyAI tool

Challenge Submission: SpeechCraft - AI-Powered Speech Analysis for Better Communication

SpeechCraft is an advanced real-time speech analytics platform that transforms spoken words into actionable insights. Using cutting-edge AI technology from AssemblyAI, it provides instant transcription while analyzing multiple dimensions of speech performance.

Read full post

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay