How to Convert a SQL Subquery to a Join

How to convert a SQL subquery to a join

This probably won't help if table is already in production but the right way to model this is to make version = 0 the permanent version and always increment the version of OLDER material. So when you insert a new version you would say:

UPDATE thetable SET version = version + 1 WHERE id = :id
INSERT INTO thetable (id, version, title, ...) VALUES (:id, 0, :title, ...)

Then this query would just be

SELECT id, title, ... FROM thetable WHERE version = 0

No subqueries, no MAX aggregation. You always know what the current version is. You never have to select max(version) in order to insert the new record.

Replacing Inner Join with a SubQuery in SQL

This is called correlated sub query.

For every row of outer query, it will run the inner query, in programming terms something like loop inside a loop.

More info..

https://www.geeksforgeeks.org/sql-correlated-subqueries/

Convert Subquery to Self Join

The result you are looking for using a self join is:

SELECT DISTINCT t1.*
FROM ma t1
JOIN ma t2  
ON  t1.SCHEDULED_ID <> t2.SCHEDULED_ID --Satisfies 2nd query
WHERE t2.ACTION_ID = 3 --Satisfies 2nd query
    OR  t1.ACTION_ID = 3 --Satisfies 1st query
ORDER BY t1.ID

how to convert left join to sub query?

In this case would actually recommend the join which should generally be quicker as long as you have proper indexes on the joining columns in both tables.

Even with subqueries, you will still want those same joins.

Size and nature of your actual data will affect performance so to know for sure you are best to test both options and measure results. However beware that the optimal query can potentially switch around as your tables grow.

SELECT b.service_status,
      (SELECT b2b_acpt_flag FROM b2b_status WHERE b.booking_id=b2b_booking_id)as b2b_acpt_flag,
      (SELECT b2b_check_in_report FROM b2b_booking_tbl  WHERE b.booking_id=gb_booking_id) as b2b_check_in_report,
      (SELECT b2b_check_in_report FROM b2b_booking_tbl  WHERE b.booking_id=gb_booking_id) as b2b_swap_flag
FROM user_booking_tb AS b
WHERE b.booking_id='$booking_id'

To dig into how this query works, you are effectively performing 3 additional queries for each and every row returned by the main query.
If b.booking_id='$booking_id' is unique, this is an extra 3 queries, but if there may be multiple entries, this could multiply and become quite slow.

Each of these extra queries will be fast, no network overhead, single row, hopefully matching on a primary key. So 3 extra queries are nominal performance, as long as quantity is low.

A join would result as a single query across 2 indexed tables, which often will shave a few milliseconds off.

Another instance where a subquery may work is where you are filtering the results rather than adding extra columns to output.

SELECT b.*
FROM user_booking_tb AS b
WHERE b.booking_id in (SELECT booking_id FROM othertable WHERE this=this and that=that)

Depending how large the typical list of booking_id's is will affect which is more efficient.

Converting subquery to Join (MySQL)

Without joins and without subqueries, using analytics:

SELECT ods.*,
       COUNT(*) OVER()          tot_ords,
       COUNT(case when order_confirmed = 'yes' then 1 else null end) OVER() yes_ords,
       COUNT(case when order_confirmed = 'no' then 1 else null end) OVER() no_ords
FROM orders ods
ORDER BY ods.order_confirmed = 'yes' DESC

Using join:

   SELECT ods.*, 
          s.tot_ords,
          s.yes_ords,
          s.no_ords 
     FROM orders ods
          cross join (select 
                             COUNT(*) tot_ords,
                             COUNT(case when order_confirmed = 'yes' then 1 else null end)  yes_ords,        
                             COUNT(case when order_confirmed = 'no' then 1 else null end)  no_ords 
                       from orders
                       ) s
    ORDER BY ods.order_confirmed = 'yes' DESC

How to convert SQL subquery into Join Clause?

Try this:

SELECT o.OrderId 
FROM Orders o
JOIN Orders ord 
ON o.OrderDate = ord.OrderDate 
WHERE ord.OrderID = 10280;

You can have a better understanding from here: https://www.sqlservertutorial.net/sql-server-basics/sql-server-self-join/#:~:text=A%20self%20join%20allows%20you,join%20or%20left%20join%20clause.

Subquery to join conversion

Well your current CTE based query actually is already using joins, but I suspect that the use of CTEs itself is the issue here. You could refactor the query by inlining and removing all the CTEs:

SELECT trr.groupId
FROM contacts c
INNER JOIN
(
    SELECT DISTINCT td.*
    FROM groups g
    INNER JOIN territoryDetails td ON td.groupId = g.id
    WHERE g.orgId = 13
) trr
    ON ST_Intersects(trr.points, c.geoPoint)
WHERE
    c.id = 567 AND c.orgId = 130;

Procedurally transform subquery into join

Converting a subquery into a JOIN can be pretty straightforward:

IN clause

 FROM TABLE_X x
WHERE x.col IN (SELECT y.col FROM TABLE_Y y)

...can be converted to:

FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col

Your JOIN criteria is where you have direct comparison.

EXISTS clause

But there are complications when you look at the EXISTS clause. EXISTS are typically correllated, where the subquery is filtered by criteria from the table(s) outside the subquery. But the EXISTS is only for returning a boolean based on the criteria.

 FROM TABLE_X x
WHERE EXISTS (SELECT NULL
                FROM TABLE_Y y
               WHERE y.col = x.col)

...converted:

FROM TABLE_X x
JOIN TABLE_Y y ON y.col = x.col

Because of the boolean, there's a risk of more rows turning up in the resultset.

SELECTs in the SELECT clause

These should always be changed, with prejudice:

SELECT x.*,
       (SELECT MAX(y.example_col)
          FROM TABLE_Y y
         WHERE y.col = x.col)
  FROM TABLE_X x

You're probably noticing a patter now, but I made this a little different for an inline view example:

SELECT x.*,
       z.mc
  FROM TABLE_X x
  JOIN (SELECT y.col, --inline view within the brackets
               MAX(y.example_col) 'mc'
          FROM TABLE_Y y
      GROUP BY y.col) z ON z.col = x.col

The key is making sure the inline view resultset includes the column(s) needed to join to, along with the columns.

LEFT JOINs

You might've noticed I didn't have any LEFT JOIN examples - this would only be necessary if columns from the subquery use NULL testing (COALESCE on almost any db these days, Oracle's NVL or NVL2, MySQLs IFNULL, SQL Server's ISNULL, etc...):

SELECT x.*,
       COALESCE((SELECT MAX(y.example_col)
          FROM TABLE_Y y
         WHERE y.col = x.col), 0)
  FROM TABLE_X x

Converted:

   SELECT x.*,
          COALESCE(z.mc, 0)
     FROM TABLE_X x
LEFT JOIN (SELECT y.col,
                  MAX(y.example_col) 'mc'
             FROM TABLE_Y y
         GROUP BY y.col) z ON z.col = x.col

Conclusion

I'm not sure if that will satisfy your typographic needs, but hope I've demonstrated that the key is determining what the JOIN criteria is. Once you know the column(s) involved, you know the table(s) involved.

---

Related Topics