I have been trying to understand this for almost over 2hrs now and still I'm unable to understand the output of my JOIN query.
I have a table gift with the structure
+--------+-------------------+-------+----------+--------------+ | giftid | giftname | price | discount | availability | +--------+-------------------+-------+----------+--------------+ | G101 | Magic Mug | 500 | 9 | 10 | | G102 | Golf Set | 3550 | 5 | 15 | | G103 | Little Astronomer | 2000 | 20 | 18 | | G104 | Renoir Paintings | 1500 | 15 | 15 | | G105 | French F | 3000 | 7 | 10 | | G106 | Magic Set | 1300 | 30 | 30 | +--------+-------------------+-------+----------+--------------+
I am running JOIN on the same table and literally comparing the same column values. So, in theory result should be 12 rows. This is true for every column except availability.
Here is my SQL query:
SELECT g1.giftid, g1.giftname, g1.discount, g1.availability FROM gift g1 JOIN gift g2 ON g1.availability=g2.availability;
If I change g1.availability=g2.availability to compare some other column, the output is 12 rows which is expected.
Can anyone explain why this is not including the column values 18 and 30? Coz they're literally the same. It is selecting every other duplicate value but not these two.
Shouldn't a self join with comparison of the same column always return all of the values present in the table twice?
Regards
Actually it gives back 10 rows as expected
only 10 and 15 are double, so you get 8 and as 18 and 30 are unique you get 2 more rows
CREATE TABLE gift ( `giftid` VARCHAR(4), `giftname` VARCHAR(17), `price` INTEGER, `discount` INTEGER, `availability` INTEGER ); INSERT INTO gift (`giftid`, `giftname`, `price`, `discount`, `availability`) VALUES ('G101', 'Magic Mug', '500', '9', '10'), ('G102', 'Golf Set', '3550', '5', '15'), ('G103', 'Little Astronomer', '2000', '20', '18'), ('G104', 'Renoir Paintings', '1500', '15', '15'), ('G105', 'French F', '3000', '7', '10'), ('G106', 'Magic Set', '1300', '30', '30');
SELECT g1.giftid, g1.giftname, g1.discount, g1.availability FROM gift g1 JOIN gift g2 ON g1.availability=g2.availability;
giftid | giftname | discount | availability :----- | :---------------- | -------: | -----------: G105 | French F | 7 | 10 G101 | Magic Mug | 9 | 10 G104 | Renoir Paintings | 15 | 15 G102 | Golf Set | 5 | 15 G103 | Little Astronomer | 20 | 18 G104 | Renoir Paintings | 15 | 15 G102 | Golf Set | 5 | 15 G105 | French F | 7 | 10 G101 | Magic Mug | 9 | 10 G106 | Magic Set | 30 | 30
SELECT g1.giftid, g1.giftname, g1.discount, g1.availability , g2.* FROM gift g1 JOIN gift g2 ON g1.availability=g2.availability;
giftid | giftname | discount | availability | giftid | giftname | price | discount | availability :----- | :---------------- | -------: | -----------: | :----- | :---------------- | ----: | -------: | -----------: G105 | French F | 7 | 10 | G101 | Magic Mug | 500 | 9 | 10 G101 | Magic Mug | 9 | 10 | G101 | Magic Mug | 500 | 9 | 10 G104 | Renoir Paintings | 15 | 15 | G102 | Golf Set | 3550 | 5 | 15 G102 | Golf Set | 5 | 15 | G102 | Golf Set | 3550 | 5 | 15 G103 | Little Astronomer | 20 | 18 | G103 | Little Astronomer | 2000 | 20 | 18 G104 | Renoir Paintings | 15 | 15 | G104 | Renoir Paintings | 1500 | 15 | 15 G102 | Golf Set | 5 | 15 | G104 | Renoir Paintings | 1500 | 15 | 15 G105 | French F | 7 | 10 | G105 | French F | 3000 | 7 | 10 G101 | Magic Mug | 9 | 10 | G105 | French F | 3000 | 7 | 10 G106 | Magic Set | 30 | 30 | G106 | Magic Set | 1300 | 30 | 30
db<>fiddle here