March 1st, 2023
JOIN Query
Different types of SQL Joins
insert into course(id, name, created_date, last_updated_date)
values(10001, 'JPA in 5 steps', LOCALTIMESTAMP, LOCALTIMESTAMP);
insert into course(id, name, created_date, last_updated_date)
values(10002, 'JDBC in 10 steps', LOCALTIMESTAMP, LOCALTIMESTAMP);
insert into course(id, name, created_date, last_updated_date)
values(10003, 'JPQL in 50 steps', LOCALTIMESTAMP, LOCALTIMESTAMP);
insert into passport(id, number)
values(40001,'E12345');
insert into passport(id, number)
values(40002,'F212345');
insert into passport(id, number)
values(40003,'GE23451');
insert into student(id, name, passport_id)
values(20001,'Ranga',40001);
insert into student(id, name, passport_id)
values(20002,'Adam',40002);
insert into student(id, name, passport_id)
values(20003,'Jane',40003);
insert into review(id, rating, description, course_id)
values(50001,'1', 'Great course', 10001);
insert into review(id, rating, description, course_id)
values(50002,'3', 'Nice course', 10001);
insert into review(id, rating, description, course_id)
values(50004,'5', 'Good you are writing', 10003);
insert into student_course(student_id, course_id)
values(20001, 10001);
insert into student_course(student_id, course_id)
values(20002, 10001);
insert into student_course(student_id, course_id)
values(20003, 10001);
insert into student_course(student_id, course_id)
values(20001, 10003);
JOIN Types
Join Course with Student
JOIN => Select c, s from Course c JOIN c.students s
(INNER) JOIN : Returns records that have matching values in both tables.
LEFT JOIN => Select c, s from Course c LEFT JOIN c.students s
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
CROSS JOIN = > 3 Course X 3 Student => 9 Result Takes all
(INNER) Join
@Test
@Transactional
public void join() {
Query query = em.createQuery("Select c, s from Course c JOIN c.students s");
List<Object[]> list = query.getResultList();
logger.info("List size: -> {}", list.size());
for(Object[] result: list) {
logger.info("Course: {} Student: {}", result[0], result[1]);
}
}
2023-03-01T20:20:41.461+05:30 INFO 384446 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : List size: -> 4
2023-03-01T20:20:41.461+05:30 INFO 384446 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-01T20:20:40.706694, createdDate=2023-03-01T20:20:40.706694) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-01T20:20:41.489+05:30 INFO 384446 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-01T20:20:40.706694, createdDate=2023-03-01T20:20:40.706694) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-01T20:20:41.490+05:30 INFO 384446 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-01T20:20:40.706694, createdDate=2023-03-01T20:20:40.706694) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-01T20:20:41.490+05:30 INFO 384446 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10003, name=JPQL in 50 steps, reviews=[Review(id=50004, rating=5, description=Good you are writing)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))], lastUpdatedDate=2023-03-01T20:20:40.709764, createdDate=2023-03-01T20:20:40.709764) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
LEFT Join
@Test
@Transactional
public void left_join() {
Query query = em.createQuery("Select c, s from Course c LEFT JOIN c.students s");
List<Object[]> list = query.getResultList();
logger.info("List size: -> {}", list.size());
for(Object[] result: list) {
logger.info("Course: {} Student: {}", result[0], result[1]);
}
}
2023-03-01T20:22:31.038+05:30 INFO 384824 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : List size: -> 5
2023-03-01T20:22:31.038+05:30 INFO 384824 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-01T20:22:30.396516, createdDate=2023-03-01T20:22:30.396516) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-01T20:22:31.071+05:30 INFO 384824 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-01T20:22:30.396516, createdDate=2023-03-01T20:22:30.396516) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-01T20:22:31.071+05:30 INFO 384824 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-01T20:22:30.396516, createdDate=2023-03-01T20:22:30.396516) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-01T20:22:31.071+05:30 INFO 384824 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-01T20:22:30.398139, createdDate=2023-03-01T20:22:30.398139) Student: null
2023-03-01T20:22:31.072+05:30 INFO 384824 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10003, name=JPQL in 50 steps, reviews=[Review(id=50004, rating=5, description=Good you are writing)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))], lastUpdatedDate=2023-03-01T20:22:30.398423, createdDate=2023-03-01T20:22:30.398423) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
Cross Join
@Test
@Transactional
public void cross_join() {
Query query = em.createQuery("Select c, s from Course c, Student s");
List<Object[]> list = query.getResultList();
logger.info("List size: -> {}", list.size());
for(Object[] result: list) {
logger.info("Course: {} Student: {}", result[0], result[1]);
}
}
2023-03-03T16:21:01.511+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : List size: -> 9
2023-03-03T16:21:01.511+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-03T16:21:00.807627, createdDate=2023-03-03T16:21:00.807627) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-03T16:21:01.541+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-03T16:21:00.807627, createdDate=2023-03-03T16:21:00.807627) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-03T16:21:01.541+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10001, name=JPA in 5 steps, reviews=[Review(id=50001, rating=1, description=Great course), Review(id=50002, rating=3, description=Nice course)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345)), Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345)), Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))], lastUpdatedDate=2023-03-03T16:21:00.807627, createdDate=2023-03-03T16:21:00.807627) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-03T16:21:01.541+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-03T16:21:00.810388, createdDate=2023-03-03T16:21:00.810388) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-03T16:21:01.542+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-03T16:21:00.810388, createdDate=2023-03-03T16:21:00.810388) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-03T16:21:01.543+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10002, name=JDBC in 10 steps, reviews=[], students=[], lastUpdatedDate=2023-03-03T16:21:00.810388, createdDate=2023-03-03T16:21:00.810388) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
2023-03-03T16:21:01.543+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10003, name=JPQL in 50 steps, reviews=[Review(id=50004, rating=5, description=Good you are writing)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))], lastUpdatedDate=2023-03-03T16:21:00.810832, createdDate=2023-03-03T16:21:00.810832) Student: Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))
2023-03-03T16:21:01.545+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10003, name=JPQL in 50 steps, reviews=[Review(id=50004, rating=5, description=Good you are writing)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))], lastUpdatedDate=2023-03-03T16:21:00.810832, createdDate=2023-03-03T16:21:00.810832) Student: Student(id=20002, name=Adam, passport=Passport(id=40002, number=F212345))
2023-03-03T16:21:01.545+05:30 INFO 93909 --- [ main] c.b.j.h.jpahibernatedemo.JPQLJoins : Course: Course(id=10003, name=JPQL in 50 steps, reviews=[Review(id=50004, rating=5, description=Good you are writing)], students=[Student(id=20001, name=Ranga, passport=Passport(id=40001, number=E12345))], lastUpdatedDate=2023-03-03T16:21:00.810832, createdDate=2023-03-03T16:21:00.810832) Student: Student(id=20003, name=Jane, passport=Passport(id=40003, number=GE23451))
Leave a Reply