-
Notifications
You must be signed in to change notification settings - Fork 0
/
java-and-databases-use-sql-joins.html
126 lines (121 loc) · 69.6 KB
/
java-and-databases-use-sql-joins.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
<!DOCTYPE html><html lang="de-ch"><head><meta charset="utf-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Java and databases use SQL joins - Finecloud</title><meta name="description" content="Introduction Let's imagine we have two Tables now, people and addresses. We want to find out how many people live at the same address. This is our current Java and SQL approach: repository/PeopleRepository.java @Override @SQL(value = FIND_BY_ID_SQL, operationType = CrudOperation.FIND_BY_ID) @SQL(value = FIND_ALL_SQL, operationType =…"><meta name="generator" content="Publii Open-Source CMS for Static Site"><link rel="stylesheet" href="https://www.finecloud.ch/media/plugins/syntaxHighlighter/prism-black.css"><link rel="canonical" href="https://www.finecloud.ch/java-and-databases-use-sql-joins.html"><link rel="alternate" type="application/atom+xml" href="https://www.finecloud.ch/feed.xml"><link rel="alternate" type="application/json" href="https://www.finecloud.ch/feed.json"><meta property="og:title" content="Java and databases use SQL joins"><meta property="og:site_name" content="Finecloud"><meta property="og:description" content="Introduction Let's imagine we have two Tables now, people and addresses. We want to find out how many people live at the same address. This is our current Java and SQL approach: repository/PeopleRepository.java @Override @SQL(value = FIND_BY_ID_SQL, operationType = CrudOperation.FIND_BY_ID) @SQL(value = FIND_ALL_SQL, operationType =…"><meta property="og:url" content="https://www.finecloud.ch/java-and-databases-use-sql-joins.html"><meta property="og:type" content="article"><link rel="shortcut icon" href="https://www.finecloud.ch/media/website/finecloud.png" type="image/png"><link rel="stylesheet" href="https://www.finecloud.ch/assets/css/style.css?v=39da73365516a098a9b73b721fc970e2"><script type="application/ld+json">{"@context":"http://schema.org","@type":"Article","mainEntityOfPage":{"@type":"WebPage","@id":"https://www.finecloud.ch/java-and-databases-use-sql-joins.html"},"headline":"Java and databases use SQL joins","datePublished":"2022-09-13T11:41","dateModified":"2022-09-13T11:41","description":"Introduction Let's imagine we have two Tables now, people and addresses. We want to find out how many people live at the same address. This is our current Java and SQL approach: repository/PeopleRepository.java @Override @SQL(value = FIND_BY_ID_SQL, operationType = CrudOperation.FIND_BY_ID) @SQL(value = FIND_ALL_SQL, operationType =…","author":{"@type":"Person","name":"Finecloud","url":"https://www.finecloud.ch/authors/finecloud/"},"publisher":{"@type":"Organization","name":"Finecloud"}}</script><meta name="google-site-verification" content="seFY9U12uiEq5U3_MyZiX6XWzk0AVFl9zITr2ZKsytY"></head><body><div class="site-container"><header class="top" id="js-header"><a class="logo" href="https://www.finecloud.ch/">Finecloud</a><nav class="navbar js-navbar"><button class="navbar__toggle js-toggle" aria-label="Menu" aria-haspopup="true" aria-expanded="false"><span class="navbar__toggle-box"><span class="navbar__toggle-inner">Menu</span></span></button><ul class="navbar__menu"><li><a href="https://www.finecloud.ch/" target="_self">Blog</a></li><li><a href="https://www.finecloud.ch/tags/" target="_self">Tags</a></li></ul></nav><div class="search"><div class="search__overlay js-search-overlay"><div class="search__overlay-inner"><form action="https://www.finecloud.ch/search.html" class="search__form"><input class="search__input js-search-input" type="search" name="q" placeholder="search..." aria-label="search..." autofocus="autofocus"></form><button class="search__close js-search-close" aria-label="Close">Close</button></div></div><button class="search__btn js-search-btn" aria-label="Search"><svg role="presentation" focusable="false"><use xlink:href="https://www.finecloud.ch/assets/svg/svg-map.svg#search"/></svg></button></div></header><main><article class="post"><div class="hero"><figure class="hero__image hero__image--overlay"><img src="https://www.finecloud.ch/media/website/download.jpg" srcset="https://www.finecloud.ch/media/website/responsive/download-xs.jpg 300w, https://www.finecloud.ch/media/website/responsive/download-sm.jpg 480w, https://www.finecloud.ch/media/website/responsive/download-md.jpg 768w, https://www.finecloud.ch/media/website/responsive/download-lg.jpg 1024w, https://www.finecloud.ch/media/website/responsive/download-xl.jpg 1360w, https://www.finecloud.ch/media/website/responsive/download-2xl.jpg 1600w" sizes="100vw" loading="eager" alt=""></figure><header class="hero__content"><div class="wrapper"><div class="post__meta"><time datetime="2022-09-13T11:41">September 13, 2022</time></div><h1>Java and databases use SQL joins</h1></div></header></div><div class="wrapper post__entry"><div class="post__toc"><h3>Table of Contents</h3><ul><li><a href="#mcetoc_1gcir99tn29">Introduction</a></li><li><a href="#mcetoc_1gcir99tn2a">What is a SQL JOIN</a></li><li><a href="#mcetoc_1gcitc0o233">How does it work</a></li></ul></div><h2 id="mcetoc_1gcir99tn29">Introduction</h2><p>Let's imagine we have two Tables now, people and addresses. We want to find out how many people live at the same address. This is our current Java and SQL approach:</p><p>repository/PeopleRepository.java</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"> <span class="hljs-meta" style="color: #bbb529;">@Override</span>
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = FIND_BY_ID_SQL, operationType = CrudOperation.FIND_BY_ID)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = FIND_ALL_SQL, operationType = CrudOperation.FIND_ALL)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = SELECT_COUNT_SQL, operationType = CrudOperation.COUNT)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = DELETE_IN_SQL, operationType = CrudOperation.DELETE_MANY)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = DELETE_SQL, operationType = CrudOperation.DELETE_ONE)
<span class="hljs-function">Person <span class="hljs-title" style="color: #ffc66d;">extractEntityFromResultSet</span><span class="hljs-params">(ResultSet rs)</span> <span class="hljs-keyword" style="color: #cc7832;">throws</span> SQLException </span>{
<span class="hljs-keyword" style="color: #cc7832;">long</span> personID = rs.getLong(<span class="hljs-string" style="color: #6a8759;">"ID"</span>);
String firstName = rs.getString(<span class="hljs-string" style="color: #6a8759;">"FIRST_NAME"</span>);
String lastName = rs.getString(<span class="hljs-string" style="color: #6a8759;">"LAST_NAME"</span>);
ZonedDateTime dob = ZonedDateTime.of(rs.getTimestamp(<span class="hljs-string" style="color: #6a8759;">"DOB"</span>).toLocalDateTime(), ZoneId.of(<span class="hljs-string" style="color: #6a8759;">"+0"</span>));
BigDecimal salary = rs.getBigDecimal(<span class="hljs-string" style="color: #6a8759;">"SALARY"</span>);
<span class="hljs-keyword" style="color: #cc7832;">long</span> homeAddressId = rs.getLong(<span class="hljs-string" style="color: #6a8759;">"HOME_ADDRESS"</span>);
Optional<Address> homeAddress = addressRepository.findById(homeAddressId);
Person person = <span class="hljs-keyword" style="color: #cc7832;">new</span> Person(personID, firstName, lastName, dob, salary);
person.setHomeAddress(homeAddress.orElse(<span class="hljs-keyword" style="color: #cc7832;">null</span>));
<span class="hljs-keyword" style="color: #cc7832;">return</span> person;
}</pre><p>repository/AddressRepository.java</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"> <span class="hljs-meta" style="color: #bbb529;">@Override</span>
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(operationType = CrudOperation.FIND_BY_ID, value= <span class="hljs-string" style="color: #6a8759;">""</span><span class="hljs-string" style="color: #6a8759;">"
SELECT ID, STREET_ADDRESS, ADDRESS2, CITY, STATE, POSTCODE, COUNTY, REGION, COUNTRY
FROM ADDRESSES
WHERE ID = ?
"</span><span class="hljs-string" style="color: #6a8759;">""</span>)
<span class="hljs-function">Address <span class="hljs-title" style="color: #ffc66d;">extractEntityFromResultSet</span><span class="hljs-params">(ResultSet rs)</span> <span class="hljs-keyword" style="color: #cc7832;">throws</span> SQLException </span>{
<span class="hljs-keyword" style="color: #cc7832;">long</span> id = rs.getLong(<span class="hljs-string" style="color: #6a8759;">"ID"</span>);
String streetAddress = rs.getString(<span class="hljs-string" style="color: #6a8759;">"STREET_ADDRESS"</span>);
String address2 = rs.getString(<span class="hljs-string" style="color: #6a8759;">"ADDRESS2"</span>);
String city = rs.getString(<span class="hljs-string" style="color: #6a8759;">"CITY"</span>);
String state = rs.getString(<span class="hljs-string" style="color: #6a8759;">"STATE"</span>);
String postcode = rs.getString(<span class="hljs-string" style="color: #6a8759;">"POSTCODE"</span>);
String county = rs.getString(<span class="hljs-string" style="color: #6a8759;">"COUNTY"</span>);
Region region = Region.valueOf(rs.getString(<span class="hljs-string" style="color: #6a8759;">"REGION"</span>).toUpperCase());
String country = rs.getString(<span class="hljs-string" style="color: #6a8759;">"COUNTRY"</span>);
Address address = <span class="hljs-keyword" style="color: #cc7832;">new</span> Address(id, streetAddress, address2, city, state, postcode, county, country, region);
<span class="hljs-keyword" style="color: #cc7832;">return</span> address;
}</pre><p>The problem with this current approve, is that we would need two SQL queries for each person record, one to get the Person and one to get the address. Since Java methods to retrieve data from an SQL DB are declared as expensive operation in terms of time and performance efficiency this is not a good approach. So how could we optimize this?</p><p>One possible answer to this problem are making use of SQL JOIN.</p><h2 id="mcetoc_1gcir99tn2a">What is a SQL JOIN</h2><p>A JOIN combines records from two tables. JOIN matches related column values in two tables. A query can contain zero, one, or multiple JOIN operations. You can already imagine that this allows us to retrieve all our wanted data by just one SQL JOIN query.</p><h2 id="mcetoc_1gcitc0o233">How does it work</h2><p>Let's look at some sample data:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-keyword" style="color: #cc7832;">SELECT</span> * <span class="hljs-keyword" style="color: #cc7832;">FROM</span> PEOPLE <span class="hljs-keyword" style="color: #cc7832;">WHERE</span> HOME_ADDRESS = <span class="hljs-number" style="color: #6897bb;">2</span>;</pre><p>output: </p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"> <span class="hljs-comment" style="color: grey;">ID</span> <span class="hljs-comment" style="color: grey;">FIRST_NAME</span> <span class="hljs-comment" style="color: grey;">LAST_NAME</span> <span class="hljs-comment" style="color: grey;">DOB</span> <span class="hljs-comment" style="color: grey;">SALARY</span> <span class="hljs-comment" style="color: grey;">DELETE_FLAG</span> <span class="hljs-comment" style="color: grey;">EMAIL</span> <span class="hljs-comment" style="color: grey;">HOME_ADDRESS</span>
<span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span>
<span class="hljs-comment" style="color: grey;">5001254</span> <span class="hljs-comment" style="color: grey;">jooonyy</span> <span class="hljs-comment" style="color: grey;">Smith</span> <span class="hljs-comment" style="color: grey;">1982</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-comment" style="color: grey;">09</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-comment" style="color: grey;">13</span> <span class="hljs-comment" style="color: grey;">00:51:54</span><span class="hljs-string" style="color: #6a8759;">.</span><span class="hljs-comment" style="color: grey;">0</span> <span class="hljs-comment" style="color: grey;">0</span> <<span class="hljs-comment" style="color: grey;">null</span>> <<span class="hljs-comment" style="color: grey;">null</span>> <span class="hljs-comment" style="color: grey;">2</span></pre><p>as we can see this record has the address foreign key 2 and if we query for this record, we get this output:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-keyword" style="color: #cc7832;">SELECT</span> * <span class="hljs-keyword" style="color: #cc7832;">FROM</span> ADDRESSES <span class="hljs-keyword" style="color: #cc7832;">WHERE</span> <span class="hljs-keyword" style="color: #cc7832;">ID</span> = <span class="hljs-number" style="color: #6897bb;">2</span>;</pre><p>output: </p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-comment" style="color: grey;">ID</span> <span class="hljs-comment" style="color: grey;">STREET_ADDRESS</span> <span class="hljs-comment" style="color: grey;">ADDRESS2</span> <span class="hljs-comment" style="color: grey;">CITY</span> <span class="hljs-comment" style="color: grey;">STATE</span> <span class="hljs-comment" style="color: grey;">POSTCODE</span> <span class="hljs-comment" style="color: grey;">COUNTY</span> <span class="hljs-comment" style="color: grey;">REGION</span> <span class="hljs-comment" style="color: grey;">COUNTRY</span>
<span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span>
<span class="hljs-comment" style="color: grey;">2</span> <span class="hljs-comment" style="color: grey;">123</span> <span class="hljs-comment" style="color: grey;">Bale</span> <span class="hljs-comment" style="color: grey;">St</span><span class="hljs-string" style="color: #6a8759;">.</span> <span class="hljs-comment" style="color: grey;">Apt</span><span class="hljs-string" style="color: #6a8759;">.</span> <span class="hljs-comment" style="color: grey;">1A</span> <span class="hljs-comment" style="color: grey;">Wala</span> <span class="hljs-comment" style="color: grey;">Wala</span> <span class="hljs-comment" style="color: grey;">WA</span> <span class="hljs-comment" style="color: grey;">90210</span> <span class="hljs-comment" style="color: grey;">Fulton</span> <span class="hljs-comment" style="color: grey;">County</span> <span class="hljs-comment" style="color: grey;">WEST</span> <span class="hljs-comment" style="color: grey;">United</span> <span class="hljs-comment" style="color: grey;">States</span></pre><p>Now, when we are joining multiple tables together, it is very common to make use of something called table aliases. And that's where we can simply give our tables and additional name or alias that we can use to refer to those individual tables columns.<br><br>The reason for this is because when you're joining two or more tables together, some of the columns of those tables may have the same names. So, for example, our people table has an ID column, so does our addresses table. So if you wanted to be able to retrieve various information from either of those particular columns, how could you specify which ID column you're talking about? The people one or the addresses one?Well, you can prefix the ID column of either of these tables with an alias.<br><br>So let's see how that would look like:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-keyword" style="color: #cc7832;">SELECT</span> * <span class="hljs-keyword" style="color: #cc7832;">FROM</span> PEOPLE <span class="hljs-keyword" style="color: #cc7832;">AS</span> PEOPLE <span class="hljs-keyword" style="color: #cc7832;">JOIN</span> ADDRESSES <span class="hljs-keyword" style="color: #cc7832;">AS</span> ADDRESS <span class="hljs-keyword" style="color: #cc7832;">ON</span> PEOPLE.HOME_ADDRESS = ADDRESS.ID;</pre><p>output:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"> <span class="hljs-comment" style="color: grey;">ID</span> <span class="hljs-comment" style="color: grey;">FIRST_NAME</span> <span class="hljs-comment" style="color: grey;">LAST_NAME</span> <span class="hljs-comment" style="color: grey;">DOB</span> <span class="hljs-comment" style="color: grey;">SALARY</span> <span class="hljs-comment" style="color: grey;">DELETE_FLAG</span> <span class="hljs-comment" style="color: grey;">EMAIL</span> <span class="hljs-comment" style="color: grey;">HOME_ADDRESS</span> <span class="hljs-comment" style="color: grey;">ID</span> <span class="hljs-comment" style="color: grey;">STREET_ADDRESS</span> <span class="hljs-comment" style="color: grey;">ADDRESS2</span> <span class="hljs-comment" style="color: grey;">CITY</span> <span class="hljs-comment" style="color: grey;">STATE</span> <span class="hljs-comment" style="color: grey;">POSTCODE</span> <span class="hljs-comment" style="color: grey;">COUNTY</span> <span class="hljs-comment" style="color: grey;">REGION</span> <span class="hljs-comment" style="color: grey;">COUNTRY</span>
<span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span>
<span class="hljs-comment" style="color: grey;">5001254</span> <span class="hljs-comment" style="color: grey;">jooonyy</span> <span class="hljs-comment" style="color: grey;">Smith</span> <span class="hljs-comment" style="color: grey;">1982</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-comment" style="color: grey;">09</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-comment" style="color: grey;">13</span> <span class="hljs-comment" style="color: grey;">00:51:54</span><span class="hljs-string" style="color: #6a8759;">.</span><span class="hljs-comment" style="color: grey;">0</span> <span class="hljs-comment" style="color: grey;">0</span> <<span class="hljs-comment" style="color: grey;">null</span>> <<span class="hljs-comment" style="color: grey;">null</span>> <span class="hljs-comment" style="color: grey;">2</span> <span class="hljs-comment" style="color: grey;">2</span> <span class="hljs-comment" style="color: grey;">123</span> <span class="hljs-comment" style="color: grey;">Bale</span> <span class="hljs-comment" style="color: grey;">St</span><span class="hljs-string" style="color: #6a8759;">.</span> <span class="hljs-comment" style="color: grey;">Apt</span><span class="hljs-string" style="color: #6a8759;">.</span> <span class="hljs-comment" style="color: grey;">1A</span> <span class="hljs-comment" style="color: grey;">Wala</span> <span class="hljs-comment" style="color: grey;">Wala</span> <span class="hljs-comment" style="color: grey;">WA</span> <span class="hljs-comment" style="color: grey;">90210</span> <span class="hljs-comment" style="color: grey;">Fulton</span> <span class="hljs-comment" style="color: grey;">County</span> <span class="hljs-comment" style="color: grey;">WEST</span> <span class="hljs-comment" style="color: grey;">United</span> <span class="hljs-comment" style="color: grey;">States</span></pre><p>It's one result set row that's combining all of the columns of the people table with all the columns of the address table for this one person. You may be wondering why did we only get one row because we didn't say anything like where home address equals four or something like that. So why did we only get one row? Well, the reason we only got one row in this particular case is because of the type of joint that is executing here. And this particular, joined by default, is what's known as an inner join. All an inner join is is that there has to be a match that satisfies this expression here on both tables, in order for a result set to be returned. Since there's only one record in the people table that has a foreign key that points to an address, and there's only one address that is being used by the people table, we only got back one result set in this particular case.<br><br>However, what if we wanted to be able to retrieve any or all people, whether or not they have a home address specified? And in fact, for our purposes in our code, that's exactly what we're going to need because when we find a person by ID, they might have an address and they might not. We don't want them to not show up just because they did not have an address. So to make that work, that's where we have to perform the left outer join, so that we can make sure that regardless of whether or not there's an address being pointed to, we always get back all of the people that we're looking for. So to do that, all we have to do is specify that we want a left outer join.</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-keyword" style="color: #cc7832;">SELECT</span> * <span class="hljs-keyword" style="color: #cc7832;">FROM</span> PEOPLE <span class="hljs-keyword" style="color: #cc7832;">AS</span> PEOPLE <span class="hljs-keyword" style="color: #cc7832;">LEFT</span> <span class="hljs-keyword" style="color: #cc7832;">OUTER</span> <span class="hljs-keyword" style="color: #cc7832;">JOIN</span> ADDRESSES <span class="hljs-keyword" style="color: #cc7832;">AS</span> ADDRESS <span class="hljs-keyword" style="color: #cc7832;">ON</span> PEOPLE.HOME_ADDRESS = ADDRESS.ID <span class="hljs-keyword" style="color: #cc7832;">FETCH</span> <span class="hljs-keyword" style="color: #cc7832;">FIRST</span> <span class="hljs-number" style="color: #6897bb;">10</span> <span class="hljs-keyword" style="color: #cc7832;">ROWS</span> <span class="hljs-keyword" style="color: #cc7832;">ONLY</span>;</pre><p>output:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;">ID FIRST_NAME LAST_NAME DOB SALARY DELETE_FLAG EMAIL HOME_ADDRESS ID STREET_ADDRESS ADDRESS2 CITY STATE POSTCODE COUNTY REGION COUNTRY
<span class="hljs-comment" style="color: grey;">-- ---------- --------- --------------------- ------ ----------- ------ ------------ ------ -------------- -------- ------ ------ -------- ------ ------ -------</span>
<span class="hljs-number" style="color: #6897bb;">12</span> Harry Jonson <span class="hljs-number" style="color: #6897bb;">1950</span><span class="hljs-number" style="color: #6897bb;">-03</span><span class="hljs-number" style="color: #6897bb;">-15</span> <span class="hljs-number" style="color: #6897bb;">10</span>:<span class="hljs-number" style="color: #6897bb;">45</span>:<span class="hljs-number" style="color: #6897bb;">10.0</span> <span class="hljs-number" style="color: #6897bb;">100000</span> <span class="hljs-literal" style="color: #6897bb;">false</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">14</span> Jack Jonson <span class="hljs-number" style="color: #6897bb;">2000</span><span class="hljs-number" style="color: #6897bb;">-01</span><span class="hljs-number" style="color: #6897bb;">-10</span> <span class="hljs-number" style="color: #6897bb;">08</span>:<span class="hljs-number" style="color: #6897bb;">45</span>:<span class="hljs-number" style="color: #6897bb;">10.0</span> <span class="hljs-number" style="color: #6897bb;">50000</span> <span class="hljs-literal" style="color: #6897bb;">false</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">15</span> Mary Jonson <span class="hljs-number" style="color: #6897bb;">2005</span><span class="hljs-number" style="color: #6897bb;">-05</span><span class="hljs-number" style="color: #6897bb;">-13</span> <span class="hljs-number" style="color: #6897bb;">17</span>:<span class="hljs-number" style="color: #6897bb;">30</span>:<span class="hljs-number" style="color: #6897bb;">10.0</span> <span class="hljs-number" style="color: #6897bb;">20000</span> <span class="hljs-literal" style="color: #6897bb;">false</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">16</span> Sabrina Jonson <span class="hljs-number" style="color: #6897bb;">1951</span><span class="hljs-number" style="color: #6897bb;">-10</span><span class="hljs-number" style="color: #6897bb;">-31</span> <span class="hljs-number" style="color: #6897bb;">19</span>:<span class="hljs-number" style="color: #6897bb;">13</span>:<span class="hljs-number" style="color: #6897bb;">43.0</span> <span class="hljs-number" style="color: #6897bb;">200000</span> <span class="hljs-literal" style="color: #6897bb;">false</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">18</span> Jake Smith <span class="hljs-number" style="color: #6897bb;">1970</span><span class="hljs-number" style="color: #6897bb;">-10</span><span class="hljs-number" style="color: #6897bb;">-31</span> <span class="hljs-number" style="color: #6897bb;">19</span>:<span class="hljs-number" style="color: #6897bb;">13</span>:<span class="hljs-number" style="color: #6897bb;">43.0</span> <span class="hljs-number" style="color: #6897bb;">75000</span> <span class="hljs-literal" style="color: #6897bb;">true</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">19</span> John Smith <span class="hljs-number" style="color: #6897bb;">1980</span><span class="hljs-number" style="color: #6897bb;">-11</span><span class="hljs-number" style="color: #6897bb;">-15</span> <span class="hljs-number" style="color: #6897bb;">21</span>:<span class="hljs-number" style="color: #6897bb;">15</span>:<span class="hljs-number" style="color: #6897bb;">00.0</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">20</span> John Smith <span class="hljs-number" style="color: #6897bb;">1980</span><span class="hljs-number" style="color: #6897bb;">-11</span><span class="hljs-number" style="color: #6897bb;">-15</span> <span class="hljs-number" style="color: #6897bb;">21</span>:<span class="hljs-number" style="color: #6897bb;">15</span>:<span class="hljs-number" style="color: #6897bb;">00.0</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">21</span> Bobby Smith <span class="hljs-number" style="color: #6897bb;">1982</span><span class="hljs-number" style="color: #6897bb;">-09</span><span class="hljs-number" style="color: #6897bb;">-13</span> <span class="hljs-number" style="color: #6897bb;">00</span>:<span class="hljs-number" style="color: #6897bb;">51</span>:<span class="hljs-number" style="color: #6897bb;">54.0</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">22</span> John Smith <span class="hljs-number" style="color: #6897bb;">1980</span><span class="hljs-number" style="color: #6897bb;">-11</span><span class="hljs-number" style="color: #6897bb;">-15</span> <span class="hljs-number" style="color: #6897bb;">21</span>:<span class="hljs-number" style="color: #6897bb;">15</span>:<span class="hljs-number" style="color: #6897bb;">00.0</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>>
<span class="hljs-number" style="color: #6897bb;">23</span> John Smith <span class="hljs-number" style="color: #6897bb;">1980</span><span class="hljs-number" style="color: #6897bb;">-11</span><span class="hljs-number" style="color: #6897bb;">-15</span> <span class="hljs-number" style="color: #6897bb;">21</span>:<span class="hljs-number" style="color: #6897bb;">15</span>:<span class="hljs-number" style="color: #6897bb;">00.0</span> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> <<span class="hljs-literal" style="color: #6897bb;">null</span>> </pre><p>But for our situation we need something like this:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-keyword" style="color: #cc7832;">SELECT</span> P.FIRST_NAME,P.LAST_NAME,A.STREET_ADDRESS,A.CITY <span class="hljs-keyword" style="color: #cc7832;">FROM</span> PEOPLE <span class="hljs-keyword" style="color: #cc7832;">AS</span> P <span class="hljs-keyword" style="color: #cc7832;">LEFT</span> <span class="hljs-keyword" style="color: #cc7832;">OUTER</span> <span class="hljs-keyword" style="color: #cc7832;">JOIN</span> ADDRESSES <span class="hljs-keyword" style="color: #cc7832;">AS</span> A <span class="hljs-keyword" style="color: #cc7832;">ON</span> P.HOME_ADDRESS = A.ID <span class="hljs-keyword" style="color: #cc7832;">WHERE</span> P.ID = <span class="hljs-number" style="color: #6897bb;">5001254</span>;</pre><p>output:</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"><span class="hljs-comment" style="color: grey;">FIRST_NAME</span> <span class="hljs-comment" style="color: grey;">LAST_NAME</span> <span class="hljs-comment" style="color: grey;">STREET_ADDRESS</span> <span class="hljs-comment" style="color: grey;">CITY</span>
<span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span> <span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span><span class="hljs-literal" style="color: #6897bb;">-</span>
<span class="hljs-comment" style="color: grey;">jooonyy</span> <span class="hljs-comment" style="color: grey;">Smith</span> <span class="hljs-comment" style="color: grey;">123</span> <span class="hljs-comment" style="color: grey;">Bale</span> <span class="hljs-comment" style="color: grey;">St</span><span class="hljs-string" style="color: #6a8759;">.</span> <span class="hljs-comment" style="color: grey;">Wala</span> <span class="hljs-comment" style="color: grey;">Wala</span></pre><p>Lets have a look how this will finally look like in our Java code:</p><p>repository/PeopleRepository.java</p><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"> <span class="hljs-keyword" style="color: #cc7832;">public</span> <span class="hljs-keyword" style="color: #cc7832;">static</span> <span class="hljs-keyword" style="color: #cc7832;">final</span> String FIND_BY_ID_SQL = <span class="hljs-string" style="color: #6a8759;">""</span><span class="hljs-string" style="color: #6a8759;">"
SELECT
P.ID, P.FIRST_NAME, P.LAST_NAME, P.DOB, P.SALARY, P.HOME_ADDRESS,
HOME.ID as HOME_ID, HOME.STREET_ADDRESS as HOME_STREET_ADDRESS, HOME.ADDRESS2 as HOME_ADDRESS2, HOME.CITY as HOME_CITY, HOME.STATE as HOME_STATE, HOME.POSTCODE as HOME_POSTCODE, HOME.COUNTY as HOME_COUNTY, HOME.REGION as HOME_REGION, HOME.COUNTRY as HOME_COUNTRY,
BIZ.ID as BIZ_ID, BIZ.STREET_ADDRESS as BIZ_STREET_ADDRESS, BIZ.ADDRESS2 as BIZ_ADDRESS2, BIZ.CITY as BIZ_CITY, BIZ.STATE as BIZ_STATE, BIZ.POSTCODE as BIZ_POSTCODE, BIZ.COUNTY as BIZ_COUNTY, BIZ.REGION as BIZ_REGION, BIZ.COUNTRY as BIZ_COUNTRY,
FROM PEOPLE AS P
LEFT OUTER JOIN ADDRESSES AS HOME ON P.HOME_ADDRESS = HOME.ID
LEFT OUTER JOIN ADDRESSES AS BIZ ON P.BIZ_ADDRESS = BIZ.ID
WHERE P.ID=?"</span><span class="hljs-string" style="color: #6a8759;">""</span>;</pre><pre class="hljs" style="color: #a9b7c6; background: #282b2e none repeat scroll 0% 0%; display: block; overflow-x: auto; padding: 0.5em;"> <span class="hljs-meta" style="color: #bbb529;">@Override</span>
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = FIND_BY_ID_SQL, operationType = CrudOperation.FIND_BY_ID)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = FIND_ALL_SQL, operationType = CrudOperation.FIND_ALL)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = SELECT_COUNT_SQL, operationType = CrudOperation.COUNT)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = DELETE_IN_SQL, operationType = CrudOperation.DELETE_MANY)
<span class="hljs-meta" style="color: #bbb529;">@SQL</span>(value = DELETE_SQL, operationType = CrudOperation.DELETE_ONE)
<span class="hljs-function">Person <span class="hljs-title" style="color: #ffc66d;">extractEntityFromResultSet</span><span class="hljs-params">(ResultSet rs)</span> <span class="hljs-keyword" style="color: #cc7832;">throws</span> SQLException </span>{
<span class="hljs-keyword" style="color: #cc7832;">long</span> personID = rs.getLong(<span class="hljs-string" style="color: #6a8759;">"ID"</span>);
String firstName = rs.getString(<span class="hljs-string" style="color: #6a8759;">"FIRST_NAME"</span>);
String lastName = rs.getString(<span class="hljs-string" style="color: #6a8759;">"LAST_NAME"</span>);
ZonedDateTime dob = ZonedDateTime.of(rs.getTimestamp(<span class="hljs-string" style="color: #6a8759;">"DOB"</span>).toLocalDateTime(), ZoneId.of(<span class="hljs-string" style="color: #6a8759;">"+0"</span>));
BigDecimal salary = rs.getBigDecimal(<span class="hljs-string" style="color: #6a8759;">"SALARY"</span>);
<span class="hljs-comment" style="color: grey;">// long homeAddressId = rs.getLong("HOME_ADDRESS");</span>
Address homeAddress = extractAddress(rs, <span class="hljs-string" style="color: #6a8759;">"HOME_"</span>);
Address bizAddress = extractAddress(rs, <span class="hljs-string" style="color: #6a8759;">"BIZ_"</span>);
Person person = <span class="hljs-keyword" style="color: #cc7832;">new</span> Person(personID, firstName, lastName, dob, salary);
person.setHomeAddress(homeAddress);
person.setBusinessAddress(bizAddress);
<span class="hljs-keyword" style="color: #cc7832;">return</span> person;
}
<span class="hljs-function"><span class="hljs-keyword" style="color: #cc7832;">private</span> Address <span class="hljs-title" style="color: #ffc66d;">extractAddress</span><span class="hljs-params">(ResultSet rs, String aliasPrefix)</span> <span class="hljs-keyword" style="color: #cc7832;">throws</span> SQLException </span>{
Long addrId = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"ID"</span>, rs, Long.class);
<span class="hljs-keyword" style="color: #cc7832;">if</span> (addrId == <span class="hljs-keyword" style="color: #cc7832;">null</span>) <span class="hljs-keyword" style="color: #cc7832;">return</span> <span class="hljs-keyword" style="color: #cc7832;">null</span>;
String streetAddress = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"STREET_ADDRESS"</span>, rs, String.class);
String address2 = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"ADDRESS2"</span>, rs, String.class);
String city = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"CITY"</span>, rs, String.class);
String state = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"STATE"</span>, rs, String.class);
String postcode = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"POSTCODE"</span>, rs, String.class);
String county = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"COUNTY"</span>, rs, String.class);
Region region = Region.valueOf(getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"REGION"</span>, rs, String.class).toUpperCase());
String country = getValueByAlias(aliasPrefix + <span class="hljs-string" style="color: #6a8759;">"COUNTRY"</span>, rs, String.class);
Address address = <span class="hljs-keyword" style="color: #cc7832;">new</span> Address(addrId, streetAddress, address2, city, state, postcode, country, county, region);
<span class="hljs-keyword" style="color: #cc7832;">return</span> address;
}
<span class="hljs-keyword" style="color: #cc7832;">private</span> <T> <span class="hljs-function">T <span class="hljs-title" style="color: #ffc66d;">getValueByAlias</span><span class="hljs-params">(String alias, ResultSet rs, Class<T> clazz)</span> <span class="hljs-keyword" style="color: #cc7832;">throws</span> SQLException </span>{
<span class="hljs-keyword" style="color: #cc7832;">int</span> columnCount = rs.getMetaData().getColumnCount();
<span class="hljs-keyword" style="color: #cc7832;">for</span> (<span class="hljs-keyword" style="color: #cc7832;">int</span> colIdX = <span class="hljs-number" style="color: #6897bb;">1</span>; colIdX < columnCount; colIdX++) {
<span class="hljs-keyword" style="color: #cc7832;">if</span> (alias.equals(rs.getMetaData().getColumnLabel(colIdX))) {
<span class="hljs-keyword" style="color: #cc7832;">return</span> (T) rs.getObject(colIdX);
}
}
<span class="hljs-keyword" style="color: #cc7832;">throw</span> <span class="hljs-keyword" style="color: #cc7832;">new</span> SQLException(String.format(<span class="hljs-string" style="color: #6a8759;">"Column not found for alias: '%s'"</span>, alias));
}</pre><p> </p></div><footer class="wrapper post__footer"><p class="post__last-updated">This article was updated on September 13, 2022</p><ul class="post__tag"><li><a href="https://www.finecloud.ch/tags/database/">database</a></li><li><a href="https://www.finecloud.ch/tags/java/">java</a></li><li><a href="https://www.finecloud.ch/tags/java-basics/">java basics</a></li><li><a href="https://www.finecloud.ch/tags/softwareentwicklung/">software development</a></li><li><a href="https://www.finecloud.ch/tags/sql/">sql</a></li></ul><div class="post__share"></div></footer></article><nav class="post__nav"><div class="post__nav-inner"><div class="post__nav-prev"><svg width="1.041em" height="0.416em" aria-hidden="true"><use xlink:href="https://www.finecloud.ch/assets/svg/svg-map.svg#arrow-prev"/></svg> <a href="https://www.finecloud.ch/java-working-with-a-database.html" class="post__nav-link" rel="prev"><span>Previous</span> Java working with databases</a></div><div class="post__nav-next"><a href="https://www.finecloud.ch/getting-stared-with-spring-framework.html" class="post__nav-link" rel="next"><span>Next</span> Getting stared with the Spring Framework </a><svg width="1.041em" height="0.416em" aria-hidden="true"><use xlink:href="https://www.finecloud.ch/assets/svg/svg-map.svg#arrow-next"/></svg></div></div></nav><div class="post__related related"><div class="wrapper"><h2 class="h5 related__title">You should also read:</h2><article class="related__item"><div class="feed__meta"><time datetime="2022-05-26T14:07" class="feed__date">Mai 26, 2022</time></div><h3 class="h1"><a href="https://www.finecloud.ch/java-collection-iteratoren.html">Java Collection Iteratoren</a></h3></article><article class="related__item"><div class="feed__meta"><time datetime="2022-05-26T13:28" class="feed__date">Mai 26, 2022</time></div><h3 class="h1"><a href="https://www.finecloud.ch/java-collection-sets.html">Java Collection Sets</a></h3></article><article class="related__item"><div class="feed__meta"><time datetime="2022-05-26T13:19" class="feed__date">Mai 26, 2022</time></div><h3 class="h1"><a href="https://www.finecloud.ch/java-collection-listen.html">Java Collection Listen</a></h3></article></div></div></main><footer class="footer"><div class="footer__copyright"><p>Powered by Publii</p></div><button onclick="backToTopFunction()" id="backToTop" class="footer__bttop" aria-label="Back to top" title="Back to top"><svg><use xlink:href="https://www.finecloud.ch/assets/svg/svg-map.svg#toparrow"/></svg></button></footer></div><script>window.publiiThemeMenuConfig = {
mobileMenuMode: 'sidebar',
animationSpeed: 300,
submenuWidth: 'auto',
doubleClickTime: 500,
mobileMenuExpandableSubmenus: true,
relatedContainerForOverlayMenuSelector: '.top',
};</script><script defer="defer" src="https://www.finecloud.ch/assets/js/scripts.min.js?v=6ca8b60e6534a3888de1205e82df8528"></script><script>var images = document.querySelectorAll('img[loading]');
for (var i = 0; i < images.length; i++) {
if (images[i].complete) {
images[i].classList.add('is-loaded');
} else {
images[i].addEventListener('load', function () {
this.classList.add('is-loaded');
}, false);
}
}</script><script defer="defer" src="https://www.finecloud.ch/media/plugins/syntaxHighlighter/prism.js"></script></body></html>