Difference in performance of two SELECTs and a SELECT plus JOIN

Question

I searched through the web to find out how a JDBC call is transmitted to the data base management system. What I want to know is, whether a JDBC call produces network traffic in order to communicate with the DBMS, even if the data base is local?

I need to argue why two separate SQL SELECT statements are more expensive than a single one with a JOIN.

Answer 1

The simple answer is using a JDBC Driver.

The driver is platform-dependent as it makes use of ODBC which in turn depends on native libraries of the underlying operating system the JVM is running upon. Also, use of this driver leads to other installation dependencies; for example, ODBC must be installed on the computer having the driver and the database must support an ODBC driver. The use of this driver is discouraged if the alternative of a pure-Java driver is available. The other implication is that any application using a type 1 driver is non-portable given the binding between the driver and platform. This technology isn't suitable for a high-transaction environment. Type 1 drivers also don't support the complete Java command set and are limited by the functionality of the ODBC driver.

Check out more on this

I need to argue why two separeat SQL SELECT statements are more expensive than a SQL single one with JOIN.

I would say that it may depend upon on the scenario. Although it is said that JOIN will almost always be better performance. But still you may find certain scenarios(although few) where SELECT statements are preferred.

In most cases we can say that Join will usually outperform multiple single SELECTS as it enables the database to do a lot of optimizations since by using JOIN the overhead is reduced and it knows how many tables it has to scan.