SQL | Joins Explained | Using Commerce tables.

This example below outlines lot of SQL Join functionality that we use in a lot of queries in our codes. The below example use commerce tables to demonstrate the joins functionality.

--EXAMPLE 2 OUTER JOIN Table XORDERITEMS:
CREATE TABLE XCATENTRY (catentry_id NUMBER,partnumber varchar2(255), lastupdate timestamp);

--2. Data insertion into table
insert into  XCATENTRY values (11,'DSC_1',sysdate-800);
insert into  XCATENTRY values (33,'DSC_3',sysdate-700);
insert into  XCATENTRY values (1010,'DSC_10',sysdate-500);
insert into  XCATENTRY values (1111,'DSC_11',sysdate-300);

output::
Tables Orders:
ORDERS_ID    PARTNUMBER    STATUS
-----------------------------------------
1        DSC_1    P
2        DSC_2    Y
3        DSC_3    c
4        DSC_4    c
5        DSC_5    c
6        DSC_4    P
7        DSC_3    y
8        DSC_7    c
9        DSC_8    P
10        DSC_9    P

Tables Catentry:
CATENTRY_ID   PARTNUMBER  LASTUPDATE
--------------------------------------------
11        DSC_1    29-08-2011 13:53:21.000000
33        DSC_3    07-12-2011 13:53:21.000000
1010        DSC_10    24-06-2012 13:53:21.000000
1111        DSC_11    10-01-2013 13:53:21.000000

Query: For fetching all partnumber from left table along with matching partnumber */

--Query 1
select o.partnumber from XORDERITEMS o left outer join xcatentry c on o.partnumber = c.partnumber;

--Query 2
select o.partnumber from XORDERITEMS o , xcatentry c where o.partnumber = c.partnumber(+);

output:
PARTNUMBER
DSC_1
DSC_3
DSC_3
DSC_4
DSC_4
DSC_8
DSC_9
DSC_7
DSC_5
DSC_2

--Query: For fetching all partnumber from right table along with matching partnumber

--QUERY
select distinct c.* from XORDERITEMS o right outer join xcatentry c on o.partnumber = c.partnumber;

--ALTERNATE QUERY:
select distinct c.* from XORDERITEMS o , xcatentry c where o.partnumber(+) = c.partnumber;

Result:
CATENTRY_ID    PARTNUMBER    LASTUPDATE
11    DSC_1    29-08-2011 13:53:21.000000
33    DSC_3    07-12-2011 13:53:21.000000
1010    DSC_10    24-06-2012 13:53:21.000000
1111    DSC_11    10-01-2013 13:53:21.000000

-- Query: For fetching all partnumber from left and right table along with matching partnumber

select o.*,c.* from XORDERITEMS o full outer  join xcatentry c on o.partnumber = c.partnumber;


ORDERS_ID    PARTNUMBER    STATUS    CATENTRY_ID    PARTNUMBER_1    LASTUPDATE
1    DSC_1    P    11    DSC_1    29-08-2011 13:53:21.000000
7    DSC_3    y    33    DSC_3    07-12-2011 13:53:21.000000
3    DSC_3    c    33    DSC_3    07-12-2011 13:53:21.000000
6    DSC_4    P
4    DSC_4    c
9    DSC_8    P
10    DSC_9    P
8    DSC_7    c
5    DSC_5    c
2    DSC_2    Y
            1111    DSC_11    10-01-2013 13:53:21.000000
            1010    DSC_10    24-06-2012 13:53:21.000000


--EXAMPLE 3 Query with date range
--Query 1: select partnumber of year 2011
select partnumber from xcatentry where to_date(to_char ( lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2011','DD/MM/YYYY')

output:
PARTNUMBER
DSC_1
DSC_3

--Query 2: select partnumber of year 2011 and 2012 but not in orders tables
select partnumber from xcatentry c where to_date(to_char ( c.lastupdate,'DD/MM/YYYY'),'DD/MM/YYYY') BETWEEN TO_DATE('01/01/2011','DD/MM/YYYY') AND TO_DATE('31/12/2012','DD/MM/YYYY')
and not exists  (select 1 from XORDERITEMS o where o.partnumber = c.partnumber)

RESULTS:
PARTNUMBER
DSC_10

--EXAMPLE 4  Query : Count the status
select status,count(*) from orders group by status order by status;

output:
STATUS    COUNT(*)
P          4
Y          1
c          4
y          1

Tables XORDERITEMS:
ORDERS_ID         PARTNUMBER   STATUS
-----------------------------------------
1                              DSC_1   P
2                              DSC_2   Y
3                              DSC_3   c
4                              DSC_4   c
5                              DSC_5   c
6                              DSC_4   P
7                              DSC_3   y
8                              DSC_7   c
9                              DSC_8   P
10                           DSC_9   P

Tables XCatentry:
CATENTRY_ID   PARTNUMBER  LASTUPDATE
--------------------------------------------
11                           DSC_1   29-08-2011 13:53:21.000000
33                           DSC_3   07-12-2011 13:53:21.000000
1010                       DSC_10 24-06-2012 13:53:21.000000
1111                       DSC_11 10-01-2013 13:53:21.000000

Tables Xcatentdesc:
CATENTRY_ID    LANGUAGE_ID SHORTDESCRIPTION
11           -1            DSC_1_DESCRIPTION
33           -1            DSC_3_DESCRIPTION
1010       -1            DSC_10_DESCRIPTION
1111       -1            DSC_11_DESCRIPTION

Query 1: Example: inner join - two table
select o.orders_id, o.partnumber,c.lastupdate from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber;
o/p:
ORDERS_ID         PARTNUMBER   LASTUPDATE
1              DSC_1   8/29/2011 1:53:21.000000 PM
3              DSC_3   12/7/2011 1:53:21.000000 PM
7              DSC_3   12/7/2011 1:53:21.000000 PM

Query 2: Example: inner join - three table
select o.orders_id, o.partnumber,cd.shortdescription from Xorders o inner join Xcatentry c on o.partnumber= c.partnumber inner join Xcatentdesc cd on c.catentry_id
= cd.catentry_id;

output:
ORDERS_ID         PARTNUMBER   SHORTDESCRIPTION
1              DSC_1   DSC_1_DESCRIPTION
3              DSC_3   DSC_3_DESCRIPTION
7              DSC_3   DSC_3_DESCRIPTION