Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pagination and INNER JOIN

I have a situation which I need to do pagination along with INNET JOIN. Here is a similar scenario I have:

DECLARE @categories AS TABLE(
    CatID INT,
    CategoryName NVARCHAR(100)
);

DECLARE @fooTable AS TABLE(
    ID INT,
    CatID INT,
    Name NVARCHAR(100),
    MinAllow INT,
    Price DECIMAL(18,2)
);

INSERT INTO @categories  VALUES(1, 'Cat1');
INSERT INTO @categories  VALUES(2, 'Cat2');
INSERT INTO @categories  VALUES(3, 'Cat3');
INSERT INTO @categories  VALUES(4, 'Cat4');
INSERT INTO @categories  VALUES(5, 'Cat5');

INSERT INTO @fooTable  VALUES(1, 1, 'Product1', 2, 112.2);
INSERT INTO @fooTable  VALUES(3, 1, 'Product3', 5, 233.32);
INSERT INTO @fooTable  VALUES(6, 1, 'Product6', 4, 12.43);
INSERT INTO @fooTable  VALUES(7, 4, 'Product7', 4, 12.43);
INSERT INTO @fooTable  VALUES(8, 5, 'Product8', 4, 12.43);

These are the records I have. As you can see, some categories do not have any products inside @fooTable. As a next step, we have the following SELECT statement:

SELECT * FROM @fooTable ft
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY CatID) AS RowNum, * FROM @categories
) AS cat ON (cat.CatID = ft.CatID);

It is a basic JOIN except that the output will also carry the row number of the categories. The result I got for this query is as follows:

ID   CatID   Name          MinAllow    Price     RowNum   CatID    CategoryName
---- ------- ------------- ----------- --------- -------- -------- -------------
1    1       Product1      2           112.20    1        1        Cat1
3    1       Product3      5           233.32    1        1        Cat1
6    1       Product6      4           12.43     1        1        Cat1
7    4       Product7      4           12.43     4        4        Cat4
8    5       Product8      4           12.43     5        5        Cat5

When you look at the RowNum column, you will see that those values are not pagination friendly. So, when I try to paginate this table as follows, I got an incorrect output:

SELECT * FROM @fooTable ft
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY CatID) AS RowNum, * FROM @categories
)AS cat ON (cat.CatID = ft.CatID) AND (cat.RowNum BETWEEN 1 AND 2);

The real situation I have is similar to this one but that query is so complicated and I need to get it working with INNER JOIN. I hope I made it clear. Any idea how I got something like that working?

Edit

According to the above result of my first select query, I should be able to retrieve products whose CatID is 1 and 4 on my second query. That's what I aim.

like image 240
tugberk Avatar asked Apr 17 '12 09:04

tugberk


People also ask

Will pagination improve performance?

Thanks to pagination, we can split our large dataset into chunks ( or pages ) that we can gradually fetch and display to the user, thus reducing the load on the database. Pagination also solves a lot of performance issues both on the client and server-side!

Does SQL support pagination?

In terms of the SQL Server, the aim of the pagination is, dividing a resultset into discrete pages with the help of the query. When the OFFSET and FETCH arguments are used in with the ORDER BY clause in a SELECT statement, it will be a pagination solution for SQL Server.

Is inner join better than subquery?

A general rule is that joins are faster in most cases (99%). The more data tables have, the subqueries are slower. The less data tables have, the subqueries have equivalent speed as joins. The subqueries are simpler, easier to understand, and easier to read.

What are the 3 types of joins?

Basically, we have only three types of joins: Inner join, Outer join, and Cross join.


1 Answers

One solution can be the next one:

SELECT  x.*
FROM
(
        SELECT  ft.*, 
                cat.CategoryName,
                DENSE_RANK() OVER (ORDER BY ft.CatID) AS Rnk
        FROM @fooTable ft
        INNER JOIN @categories cat ON (cat.CatID = ft.CatID)
) AS x
WHERE   x.Rnk BETWEEN 1 AND 2

Results:

ID CatID Name     MinAllow Price   CategoryName Rnk
-- ----- -------- -------- ------- ------------ ---
1  1     Product1 2        112.20  Cat1         1
3  1     Product3 5        233.32  Cat1         1
6  1     Product6 4        12.43   Cat1         1
7  4     Product7 4        12.43   Cat4         2
like image 174
Bogdan Sahlean Avatar answered Sep 25 '22 01:09

Bogdan Sahlean