Menu
I am selecting from a table and doing a left join with a many to one relationship.
My problem to solve is with paging. I want to do paging on tableA only.
Example:
SELECT *
FROM tableA
[WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
This will return rows 0 - 9 of tableA. Perfect.
Now the problem is when I introduce the join. I still want tableA's rows 1-10 but when I do the join it introduces extra rows as expected since tableB will have multiple entries to join against each tableA row. So now I no longer get the same rows from tableA, I may only get the first 2 rows but have 10 total because of the join.
SELECT *
FROM tableA
LEFT JOIN tableB ON foo = bar
[WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
I want to get back as many rows as the join produces but only on tableA's 0-9 (or 10-19)
To clarify, there are multiple tableB rows for each tableA row. I want to select and page based on tableA only but still get back an entry for all the joins on tableB.
719
The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.
The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key. If there's no matching row, return null.
Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.
You can use inner query in this case
SELECT *
FROM (
SELECT * FROM tableA [WHERE HERE] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
) q
JOIN tableB on foo = bar [WHERE HERE]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
