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 Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

👋 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