JDBC Join Example in Java: Inner, Left and Right Join with MySQL
Most Java beginners learn how to insert and select from a single table. But real applications have many tables that relate to each other. To fetch that related data, you need joins. In this guide, I will show you a complete JDBC join example in Java — inner join, left join, and right join — using MySQL, PreparedStatement, and a clean DAO pattern. By the end, you will understand how to combine tables in JDBC the right way.
What is a JDBC join?
Imagine you have two tables in your database. One table stores student details — roll number, name, marks. Another table stores certificates — roll number, certificate name, year. The roll number connects the two tables.
Now suppose you want to see every student's name alongside their certificate names. You could run two separate queries: one for students, one for certificates. Then you would have to match them up in Java code. That is slow and messy.
A join does this matching inside the database. You write one SQL query that combines the rows from both tables based on the related column. JDBC simply sends that query and gives you the combined result.
That is the whole idea. One query, combined data, no manual matching in Java.
There are three join types you will use most often:
- Inner join — only rows that match in both tables
- Left join — all rows from the left table, plus matches from the right
- Right join — all rows from the right table, plus matches from the left
We will write all three using JDBC PreparedStatement.
The database design for this example
We will use three tables in a MySQL database called joinexample:
student
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| roll | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| marks | float | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+address
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| pincode | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+certificate
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| roll | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| year | varchar(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+Notice that roll appears in all three tables. That is the column we will join on. One student can have one address, but many certificates. That is why certificate is a separate table.
Entity classes: Student, Address, Certificate
Before we write SQL, we need Java classes to hold the data. These are called entity classes or POJOs (Plain Old Java Objects).
Student.java
public class Student {
private int roll;
private String name;
private float marks;
private Address address;
private Certificate[] certificates;
public int getRoll() {
return roll;
}
public void setRoll(int roll) {
this.roll = roll;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getMarks() {
return marks;
}
public void setMarks(float marks) {
this.marks = marks;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public Certificate[] getCertificates() {
return certificates;
}
public void setCertificates(Certificate[] certificates) {
this.certificates = certificates;
}
}Address.java
public class Address {
private String name;
private int pincode;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPincode() {
return pincode;
}
public void setPincode(int pincode) {
this.pincode = pincode;
}
}Certificate.java
public class Certificate {
private String name;
private String year;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
}These classes have nothing to do with JDBC. They are just containers for data. Keeping them separate from database code is good practice — that is what the DAO pattern is about.
Dao class — getting the connection
The Dao class handles the database connection. Every DAO class in our example will call Dao.getMyConnection() to get a Connection object.
import java.sql.Connection;
import java.sql.DriverManager;
public class Dao {
private static Connection con;
public static Connection getMyConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/joinexample", "root", "root");
} catch (Exception e) {
System.out.println(e);
}
return con;
}
}Notice the driver class: com.mysql.jdbc.Driver. For MySQL 5.x this works. For MySQL 8.x, use com.mysql.cj.jdbc.Driver and add the connector JAR to your classpath.
The URL format is: jdbc:mysql://host:port/database. Replace root with your MySQL username and password.
Inserting data into three tables
Before we can join, we need data. The StudentDaoJoinQuery class has an insert method that inserts into all three tables in one call.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class StudentDaoJoinQuery {
public int insert(Student std) {
int callback = 0;
try (Connection con = Dao.getMyConnection();) {
PreparedStatement pstudent = con.prepareStatement(
"insert into student values(?,?,?)");
pstudent.setInt(1, std.getRoll());
pstudent.setString(2, std.getName());
pstudent.setFloat(3, std.getMarks());
PreparedStatement paddress = con.prepareStatement(
"insert into address values(?,?,?)");
paddress.setInt(1, std.getRoll());
paddress.setString(2, std.getAddress().getName());
paddress.setInt(3, std.getAddress().getPincode());
Certificate[] certificates = std.getCertificates();
for (int i = 0; i < certificates.length; i++) {
Certificate certificate = certificates[i];
PreparedStatement pcertificate = con.prepareStatement(
"insert into certificate values(?,?,?)");
pcertificate.setInt(1, std.getRoll());
pcertificate.setString(2, certificate.getName());
pcertificate.setString(3, certificate.getYear());
int pc = pcertificate.executeUpdate();
}
paddress.executeUpdate();
pstudent.executeUpdate();
callback = 1;
} catch (Exception e) {
System.out.println(e);
}
return callback;
}Notice how we use one Connection for all three inserts. This is important — it means the whole thing can be wrapped in a transaction if needed. Also notice the try-with-resources syntax: try (Connection con = ...) automatically closes the connection when the block ends.
Inner join in JDBC
An inner join returns only the rows where the join condition matches in both tables. If a student has no certificate, that student will not appear. If a certificate has no matching student, that certificate will not appear.
Here is the method:
public void innerJoin() {
try (Connection con = Dao.getMyConnection();) {
PreparedStatement ps = con.prepareStatement(
"select certificate.name,certificate.year,student.name FROM certificate " +
"INNER JOIN student ON certificate.roll=student.roll");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String cname = rs.getString(1);
String cyear = rs.getString(2);
String sname = rs.getString(3);
System.out.println(cname + " " + cyear + " " + sname);
}
} catch (Exception e) {
System.out.println(e);
}
}Look at the SQL:
select certificate.name, certificate.year, student.name
FROM certificate
INNER JOIN student ON certificate.roll = student.rollWe select three columns: certificate name, certificate year, and student name. The join condition is certificate.roll = student.roll. Only rows where both tables have the same roll number will appear.
In the ResultSet, we read columns by index: rs.getString(1), rs.getString(2), rs.getString(3). Using index is safer than column name after a join, because both tables have a name column and the names would collide.
Left join in JDBC
A left join returns all rows from the left table, plus matching rows from the right table. If there is no match, the right side is filled with NULL.
In our example, the left table is certificate. So we get every certificate, even if no student matches.
public void leftJoin() {
try (Connection con = Dao.getMyConnection();) {
PreparedStatement ps = con.prepareStatement(
"select certificate.name,certificate.year,student.name FROM " +
"certificate LEFT JOIN student ON certificate.roll=student.roll");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String cname = rs.getString(1);
String cyear = rs.getString(2);
String sname = rs.getString(3);
System.out.println(cname + " " + cyear + " " + sname);
}
} catch (Exception e) {
System.out.println(e);
}
}Notice the only change is LEFT JOIN instead of INNER JOIN. That one word changes the result completely.
When there is no matching student, sname will be null. You should always check for null before using the value, or handle it gracefully.
Right join in JDBC
A right join returns all rows from the right table, plus matching rows from the left table. If there is no match, the left side is filled with NULL.
In our example, the right table is student. So we get every student, even if they have no certificate.
public void rightJoin() {
try (Connection con = Dao.getMyConnection();) {
PreparedStatement ps = con.prepareStatement(
"select certificate.name,certificate.year,student.name FROM " +
"certificate RIGHT JOIN student ON certificate.roll=student.roll");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String cname = rs.getString(1);
String cyear = rs.getString(2);
String sname = rs.getString(3);
System.out.println(cname + " " + cyear + " " + sname);
}
} catch (Exception e) {
System.out.println(e);
}
}Now every student appears, even those without certificates. The certificate columns will be null for those rows.
If you prefer to avoid right joins (some developers do), you can swap the table order and use a left join instead. The result is the same. Many teams standardize on left joins only, for readability.
When to use each join type
Use Inner Join when:
- You only want rows that exist in both tables
- You are joining a parent and child table where every child must have a parent
- You do not want any
NULLvalues from the missing side
Use Left Join when:
- You want all rows from the main table, even those without a match
- You are building a report where missing data should still appear
- You want to find rows in the left table that have no match in the right table (by checking for
NULL)
Use Right Join when:
- The right table is your main table
- You want all rows from the right table regardless of matches
- You can also flip the tables and use a left join instead — same result
In real projects, inner joins and left joins cover most of your needs. Right joins are less common because you can always rewrite them as left joins by swapping the table order.
Common questions
What is a JDBC join in simple words?
A JDBC join is a SQL query that combines rows from two or more tables based on a related column, executed through Java's JDBC API. It lets you fetch related data in one query instead of multiple round trips to the database.
What is the difference between inner join, left join and right join?
Inner join returns only rows that match in both tables. Left join returns all rows from the left table plus matching rows from the right table. Right join returns all rows from the right table plus matching rows from the left table.
Why use PreparedStatement for join queries?
PreparedStatement prevents SQL injection, handles data type conversion automatically, and lets the database cache the query plan for better performance. It is the recommended way to run any SQL query in JDBC.
What is the DAO pattern in JDBC?
DAO stands for Data Access Object. It is a design pattern that separates database code from business logic. All SQL queries live in the DAO class, keeping your service classes clean and testable.
How do I read join results from a ResultSet?
Use the ResultSet getString, getInt, getFloat methods with either the column name or the column index. After a join, column names may repeat, so using the column index (1, 2, 3) is often safer.
Which MySQL driver class do I use for JDBC?
For MySQL 5.x, the driver class is com.mysql.jdbc.Driver. For MySQL 8.x, use com.mysql.cj.jdbc.Driver. Both work with the JDBC API, but newer versions should use the cj driver.
Where to go next
You now know how to write inner, left, and right joins in JDBC using PreparedStatement and a clean DAO structure. You understand the entity classes, the connection handling, and how to read joined results from a ResultSet.
If you remember only one thing, remember this: A join is just one SQL query that combines tables. JDBC does not care — it sends the query and gives you the ResultSet.
The real learning happens when you set up the MySQL tables yourself, insert a few rows, and run each join type. See the difference in the output. That is how the concept sticks.
If this helped, bookmark it. Next, try adding a WHERE clause to filter the joined results, or try joining more than two tables. The pattern is the same.
No comments:
Post a Comment