0

apologize if there is the same question.

When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN".

Now I am wondering is it any performance differences between them. Why do I need to use WITH clause?

Update: I am not sure it is the exact comparation that we can use both and check any performance differences but here is an example:

  WITH query_name1 AS (
         SELECT ... FROM table_1
         )
       , query_name2 AS (
         SELECT ...
           FROM table_2
            ...
         )
    SELECT ...

vs

Select ... FROM (
SELECT ... FROM table_1 
LEFT JOIN table_2
)
Improve this question
3
  • 1
    Can you add two examples? What programming language and what database engine you are asking the question about?
    – SqlWorldWide
    Commented Sep 29, 2021 at 20:06
  • 5
    "you don't need to use "WITH", you can do anything with "JOIN"." - as stated, that is plain wrong. That statement is only true if each CTE ("with" clause) is used only once. But as soon as you use recursive CTEs or use a CTE more than once, you can't do this with a derived table. It also makes a huge difference if you are using Postgres or Redshift. Please add only one tag for the DBMS you are really using
    – user1822
    Commented Sep 29, 2021 at 21:10
  • @a_horse_with_no_name thank you for your comment. The first I have edited tags so mainly I am using "Redshift"
    – Axis
    Commented Sep 29, 2021 at 21:33

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.