Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Application Programming › SQL LEFT JOIN and WHERE clauses
New Posts  All Forums:Forum Nav:

SQL LEFT JOIN and WHERE clauses

post #1 of 7
Thread Starter 
This sql statement:

SELECT field FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.name = "foobar";

should be equivalent to this sql statement:

SELECT field FROM table1 INNER JOIN table2 ON table1.id = table2.id AND table2.name = "foobar";

If yes, what is the purpose of using LEFT JOIN in this case then?
AMD Haus
(8 items)
 
Lynnfield
(12 items)
 
m9040n.2
(13 items)
 
CPUMotherboardGraphicsRAM
AMD FX-4100 Gigabyte GA-970A-UD3 XFX Radeon HD 6870 1GD5 Crucial Ballistix PC3-14900 2GB x 2 
Hard DriveCoolingPowerCase
OCZ Vertex Plus 120GB, Hitachi 7K1000.D 500GB Cooler Master Hyper 212+ (Push/Pull) Seasonic S12II Bronze 620W BitFenix Merc Alpha 
CPUMotherboardGraphicsRAM
Intel Core i5-760 Gigabyte GA-P55A-UD3 Asus EAH6850 DirectCU 1GB DDR5 G.Skill Ripjaws PC3-12800 2GB x 2 
Hard DriveCoolingPowerCase
Western Digital Caviar Black 640GB Cooler Master Hyper 212+ (Push) Corsair TX-750 Cooler Master CM690 II Advanced 
CPUMotherboardGraphicsRAM
Intel Core2Quad Q6600 Asus Berkeley MSI N460GTX Cyclone 1GD5 Unknown Brand PC2-5300 3GB 
Hard DriveCoolingPowerCase
Hitachi 7K500 320GB x 2 Stock OCZ ModXStream 700W HP Pavillion 
  hide details  
Reply
AMD Haus
(8 items)
 
Lynnfield
(12 items)
 
m9040n.2
(13 items)
 
CPUMotherboardGraphicsRAM
AMD FX-4100 Gigabyte GA-970A-UD3 XFX Radeon HD 6870 1GD5 Crucial Ballistix PC3-14900 2GB x 2 
Hard DriveCoolingPowerCase
OCZ Vertex Plus 120GB, Hitachi 7K1000.D 500GB Cooler Master Hyper 212+ (Push/Pull) Seasonic S12II Bronze 620W BitFenix Merc Alpha 
CPUMotherboardGraphicsRAM
Intel Core i5-760 Gigabyte GA-P55A-UD3 Asus EAH6850 DirectCU 1GB DDR5 G.Skill Ripjaws PC3-12800 2GB x 2 
Hard DriveCoolingPowerCase
Western Digital Caviar Black 640GB Cooler Master Hyper 212+ (Push) Corsair TX-750 Cooler Master CM690 II Advanced 
CPUMotherboardGraphicsRAM
Intel Core2Quad Q6600 Asus Berkeley MSI N460GTX Cyclone 1GD5 Unknown Brand PC2-5300 3GB 
Hard DriveCoolingPowerCase
Hitachi 7K500 320GB x 2 Stock OCZ ModXStream 700W HP Pavillion 
  hide details  
Reply
post #2 of 7
EQ if table1 and table2 had same records (id) both table1 and table2.

In other hand not EQ if table1 and table2 had not same records (id) both table1 and table2.

sory i noop tongue.gif
post #3 of 7
Quote:
Originally Posted by thehybridkiwi View Post

This sql statement:
SELECT field FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.name = "foobar";
should be equivalent to this sql statement:
SELECT field FROM table1 INNER JOIN table2 ON table1.id = table2.id AND table2.name = "foobar";
If yes, what is the purpose of using LEFT JOIN in this case then?

You're right that they are equivalent, but only because you shouldn't be using LEFT JOIN there. LEFT JOIN allows NULL in the join field on the right table only, while RIGHT JOIN allows NULL in the join field on the left only. In the first query, by placing a constraint on a table2 column and not allowing null values, you ensure that table2.id will never be null, making it equivalent to an INNER JOIN.
     
CPUMotherboardGraphicsRAM
Intel Core i7-4770K Gigabyte G1.Sniper M5 EVGA GTX 780 SC Crucial Ballistix Sport 2 x 8GB 
Hard DriveHard DriveHard DriveOptical Drive
Samsung 840 Pro 256GB Seagate Barracuda 3TB Seagate Barracuda 4TB Asus BD-ROM 
CoolingOSMonitorKeyboard
Noctua NH-D14 Windows 8 Pro Dell U2713HM Ducky YOTD (MX Brown) 
PowerCaseMouseMouse Pad
SeaSonic SS-760XP2 Fractal Design Arc Mini Roccat Savu SteelSeries 9HD 
Audio
Beyerdynamic DT990 250 Ohm 
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion HDHomeRun Prime 
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Asus M5A99X EVO Sapphire HD 7950 G.Skill 8GB DDR3 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Lepa G700-MA Corsair 500R Razer Abyssus 
Mouse Pad
Razer Goliathus Speed 
  hide details  
Reply
     
CPUMotherboardGraphicsRAM
Intel Core i7-4770K Gigabyte G1.Sniper M5 EVGA GTX 780 SC Crucial Ballistix Sport 2 x 8GB 
Hard DriveHard DriveHard DriveOptical Drive
Samsung 840 Pro 256GB Seagate Barracuda 3TB Seagate Barracuda 4TB Asus BD-ROM 
CoolingOSMonitorKeyboard
Noctua NH-D14 Windows 8 Pro Dell U2713HM Ducky YOTD (MX Brown) 
PowerCaseMouseMouse Pad
SeaSonic SS-760XP2 Fractal Design Arc Mini Roccat Savu SteelSeries 9HD 
Audio
Beyerdynamic DT990 250 Ohm 
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion HDHomeRun Prime 
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Asus M5A99X EVO Sapphire HD 7950 G.Skill 8GB DDR3 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Lepa G700-MA Corsair 500R Razer Abyssus 
Mouse Pad
Razer Goliathus Speed 
  hide details  
Reply
post #4 of 7
While both queries are equivalent, the first one will run considerably slower than the second.
The first one will join all records including null ones on the right & then filter out the results based on the where clause. The second query will join both tables based on the matching records in the where clause.
My System
(13 items)
 
  
CPUMotherboardGraphicsRAM
i5 2500k ASUS - P8Z68-V/GEN3 GTX 770 DDR3 - 8G 
Hard DriveOSMonitorPower
WD Green Win 7 x64 Dell ST2320L Corsair 750TX 
CaseMouse
Coolermaster 310 Logitech G502 
  hide details  
Reply
My System
(13 items)
 
  
CPUMotherboardGraphicsRAM
i5 2500k ASUS - P8Z68-V/GEN3 GTX 770 DDR3 - 8G 
Hard DriveOSMonitorPower
WD Green Win 7 x64 Dell ST2320L Corsair 750TX 
CaseMouse
Coolermaster 310 Logitech G502 
  hide details  
Reply
post #5 of 7
Quote:
Originally Posted by _Nikhil View Post

While both queries are equivalent, the first one will run considerably slower than the second.
The first one will join all records including null ones on the right & then filter out the results based on the where clause. The second query will join both tables based on the matching records in the where clause.

I agree that, in theory, the first one would run slower. In practice, the DBMS optimizes the queries at runtime and it may not actually do more work. To verify this, I just tested this on a relatively large SQL Server database. Both complete in the same 11 seconds and return roughly 90,000 rows. This was tested twice on four independent data sets from "table2," in order to avoid the benefits of caching. To confirm that it's not a problem of the specific tables in question, I also tried running the LEFT JOIN without the WHERE clause. I stopped it at the 4 minute mark, which returned over 1,900,000 rows.

I'll concede that this is relying on runtime optimizations of the DBMS and may be software-dependent. Also, in a significantly more complex query, the first one might take slightly longer.
     
CPUMotherboardGraphicsRAM
Intel Core i7-4770K Gigabyte G1.Sniper M5 EVGA GTX 780 SC Crucial Ballistix Sport 2 x 8GB 
Hard DriveHard DriveHard DriveOptical Drive
Samsung 840 Pro 256GB Seagate Barracuda 3TB Seagate Barracuda 4TB Asus BD-ROM 
CoolingOSMonitorKeyboard
Noctua NH-D14 Windows 8 Pro Dell U2713HM Ducky YOTD (MX Brown) 
PowerCaseMouseMouse Pad
SeaSonic SS-760XP2 Fractal Design Arc Mini Roccat Savu SteelSeries 9HD 
Audio
Beyerdynamic DT990 250 Ohm 
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion HDHomeRun Prime 
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Asus M5A99X EVO Sapphire HD 7950 G.Skill 8GB DDR3 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Lepa G700-MA Corsair 500R Razer Abyssus 
Mouse Pad
Razer Goliathus Speed 
  hide details  
Reply
     
CPUMotherboardGraphicsRAM
Intel Core i7-4770K Gigabyte G1.Sniper M5 EVGA GTX 780 SC Crucial Ballistix Sport 2 x 8GB 
Hard DriveHard DriveHard DriveOptical Drive
Samsung 840 Pro 256GB Seagate Barracuda 3TB Seagate Barracuda 4TB Asus BD-ROM 
CoolingOSMonitorKeyboard
Noctua NH-D14 Windows 8 Pro Dell U2713HM Ducky YOTD (MX Brown) 
PowerCaseMouseMouse Pad
SeaSonic SS-760XP2 Fractal Design Arc Mini Roccat Savu SteelSeries 9HD 
Audio
Beyerdynamic DT990 250 Ohm 
CPUMotherboardGraphicsRAM
AMD A6-3500 Asrock A75M HD 6530D Samsung 4GB DDR3 1600  
Hard DriveHard DriveHard DriveOptical Drive
Intel 320 80GB Samsung Spinpoint F4 2TB WD Caviar Green 3TB Sony BD-Rom 
CoolingOSMonitorKeyboard
Noctua NH-C12P SE14 Win 7 Professional 64-bit Samsung LN46C600 Lenovo N5902 
PowerCaseOther
Corsair CX430 Antec Veris Fusion HDHomeRun Prime 
CPUMotherboardGraphicsRAM
AMD Phenom II X3 B55 Asus M5A99X EVO Sapphire HD 7950 G.Skill 8GB DDR3 
Hard DriveOptical DriveCoolingMonitor
Samsung F3 1TB LG DVD-RW CM Hyper 212 Plus Dell U2312HM 
KeyboardPowerCaseMouse
Pink KBT Race (MX Blue) Lepa G700-MA Corsair 500R Razer Abyssus 
Mouse Pad
Razer Goliathus Speed 
  hide details  
Reply
post #6 of 7
Inner join will only return rows when table1 and table2 have rows with matching IDs that also match your where clause. Left (outer) join would always return all results from table1 (that match your where clause) with 0 or many matching rows from table2 having the same Id from matching rows in table1.
NegativEnergy
(21 items)
 
  
CPUMotherboardGraphicsRAM
2500K @ 4.6 (1.34v) ASRock Z68 PRO GEN3 Z68 SLI MSI GTX560 TI x2 Cosair Vengeance 16gb 1600 
Hard DriveHard DriveCoolingCooling
Crucial M4 128gb Western Digital Green 2TB EK HF rev 3 CPU Block (Plexi + EN Nickle) EK HF Bridge edition Universal VGA Block (x2) 
CoolingCoolingCoolingCooling
Danger Den CPX Pro Pump XSPC RX360 Radiator (rev 2) XSPC RX240 Radiator 7x PrimoChill Blood Red Dye bomb in 110oz of di... 
CoolingOSMonitorKeyboard
1x Primochill Utopia Win 7 64bit SP1 acer hn274h 120hz 3d 27" Steelseries 6gv2 
PowerCaseMouse
OCZ ZX850w gold HAF X MS 1.0 5 Button 
  hide details  
Reply
NegativEnergy
(21 items)
 
  
CPUMotherboardGraphicsRAM
2500K @ 4.6 (1.34v) ASRock Z68 PRO GEN3 Z68 SLI MSI GTX560 TI x2 Cosair Vengeance 16gb 1600 
Hard DriveHard DriveCoolingCooling
Crucial M4 128gb Western Digital Green 2TB EK HF rev 3 CPU Block (Plexi + EN Nickle) EK HF Bridge edition Universal VGA Block (x2) 
CoolingCoolingCoolingCooling
Danger Den CPX Pro Pump XSPC RX360 Radiator (rev 2) XSPC RX240 Radiator 7x PrimoChill Blood Red Dye bomb in 110oz of di... 
CoolingOSMonitorKeyboard
1x Primochill Utopia Win 7 64bit SP1 acer hn274h 120hz 3d 27" Steelseries 6gv2 
PowerCaseMouse
OCZ ZX850w gold HAF X MS 1.0 5 Button 
  hide details  
Reply
post #7 of 7
Quote:
Originally Posted by thehybridkiwi View Post

If yes, what is the purpose of using LEFT JOIN in this case then?

There is no purpose except to show how flexible sql can be. However, I've also seen how such flexibility in querying can be a disaster.

I avoid outer joins wherever possible and always place as many search terms in my inner joins as I go along which helps to limit the record counts within the joins. I like to help limit the amount of rows as the query progresses, but many people (read: that damn colleague of mine!) love to get as much stuff together before looking through it. If you're looking for a needle in the haystack, cut back on the hay.

If this is on MS SQLServer then it's a good idea to look through the execution plan for different (yet potentially equivalent) queries such as this. They might run in the same amount of time, but you'd be surprised how much more reading/writing/searching one of them could be doing.
The Aged
(24 items)
 
  
CPUMotherboardGraphicsRAM
AMD Phenom II X4 955BE (C2) 3.2GHz ASUS M4A78T-E MSI N220GT-MD1G/D3 Kingston ValueRAM DDR3-1333 2x2GB 
Hard DriveHard DriveHard DriveHard Drive
Western Digital Caviar Blue WD3200AAKS 320GB Hitachi HUA7210SASUN1.0T 1TB Seagate ST31000528AS 1TB Western Digital Caviar Black WD5001AALS 500GB 
Hard DriveHard DriveOptical DriveCooling
Western Digital Caviar Black WD1001FALS 1TB Western Digital Caviar Green WD10EARS 1TB SONY DVD writer Corsair H50 + Sanyo Denki San Ace 120 PWM 
CoolingCoolingCoolingOS
Thermalright HR-05/IFX DeepCool V400 graphics card cooler Coolermaster 12cm SickleFlow fan for HDDs Microsoft Windows 7 Ultimate x64 
MonitorKeyboardPowerCase
Dell U2711 27" IPS Logitech Dinovo bluetooth keyboard Vantec VAN-600AS ion2+ 600W Coolermaster Stacker CM-830 Black 
MouseMouse PadAudioOther
Logitech MX1100R Razer something or other. Stock onboard audio, Audio-technica ATH-A900, D... Wife + kid 
  hide details  
Reply
The Aged
(24 items)
 
  
CPUMotherboardGraphicsRAM
AMD Phenom II X4 955BE (C2) 3.2GHz ASUS M4A78T-E MSI N220GT-MD1G/D3 Kingston ValueRAM DDR3-1333 2x2GB 
Hard DriveHard DriveHard DriveHard Drive
Western Digital Caviar Blue WD3200AAKS 320GB Hitachi HUA7210SASUN1.0T 1TB Seagate ST31000528AS 1TB Western Digital Caviar Black WD5001AALS 500GB 
Hard DriveHard DriveOptical DriveCooling
Western Digital Caviar Black WD1001FALS 1TB Western Digital Caviar Green WD10EARS 1TB SONY DVD writer Corsair H50 + Sanyo Denki San Ace 120 PWM 
CoolingCoolingCoolingOS
Thermalright HR-05/IFX DeepCool V400 graphics card cooler Coolermaster 12cm SickleFlow fan for HDDs Microsoft Windows 7 Ultimate x64 
MonitorKeyboardPowerCase
Dell U2711 27" IPS Logitech Dinovo bluetooth keyboard Vantec VAN-600AS ion2+ 600W Coolermaster Stacker CM-830 Black 
MouseMouse PadAudioOther
Logitech MX1100R Razer something or other. Stock onboard audio, Audio-technica ATH-A900, D... Wife + kid 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Application Programming
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Application Programming › SQL LEFT JOIN and WHERE clauses