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 ... 


Select ... FROM (
SELECT ... FROM table_1 
LEFT JOIN table_2
  • 1
    Can you add two examples? What programming language and what database engine you are asking the question about? Sep 29 '21 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 Sep 29 '21 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
    Sep 29 '21 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.