SQL supports three types of join operation. Most developers learn the inner join first. But there are two other join operations you should know about. These are the outer join, and the full outer join. These additional join types allow you to write in essence could be termed as optional joins.
The so-called inner-join is the default. It's the happy path from a theory perspective, and it's the join type most SQL developers learn first. Use it to combine related rows from two or more tables.
For example, perhaps you want to report on all the customers in the AdventureWorks database. You might begin working that business problem by writing the following query:
SELECT c.CustomerID, p.FirstName, p.LastName FROM Sales.Customer AS c JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID CustomerID FirstName LastName ----------- ----------- ----------- 29485 Catherine Abel 29486 Kim Abercrombie 29487 Humberto Acevedo ...
Implicit in this query is the presumption that all customers are persons. The inner join operation will not return customers who lack a corresponding row in the Person table. Yet there are customers in AdventureWorks without a corresponding row in Person. The implicit presumption that all customers are persons is wrong, and we need some other join operation capable of correctly addressing the business requirement by listing all the customers.
Our solution lies in the outer join, which perhaps ought to have been termed the optional join. Designate a driving table using the keywords LEFT and RIGHT. Then the join to the other table becomes optional. For example, we can report on all customers – whether persons or not – by writing the following left outer join:
SELECT c.CustomerID, p.FirstName, p.LastName FROM Sales.Customer AS c LEFT JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID CustomerID FirstName LastName ----------- ----------- ----------- 29485 Catherine Abel 29486 Kim Abercrombie 29487 Humberto Acevedo ... 1 NULL NULL 2 NULL NULL 3 NULL NULL
The keywords LEFT JOIN specify the table written to the left as the driving table. That's the Customer table, so we'll get all rows from that table in our result. Whenever possible, those rows are joined to the Person table. Nulls are returned when there are no corresponding Person rows.
Left and right joins are the same operation. The only difference lies in the order in which you mention the tables in your query. The preceding left join can be rewritten as a right join by mentioning the tables in the opposite order:
SELECT c.CustomerID, p.FirstName, p.LastName FROM Person.Person AS p RIGHT JOIN Sales.Customer AS c ON c.PersonID = p.BusinessEntityID
It's common to spell out LEFT OUTER JOIN and RIGHT OUTER JOIN. The word OUTER is optional in the syntax, but using it helps remind you and other developers maintaining the code that an optional join is in play.
Both queries in this section give the same result. However, it's customary in practice to write all such joins as left joins. I've yet to encounter right joins in production code. I'm sure they are out there, but it's better in my opinion to stay with left joins and avoid confusing future developers.
It's rare, but it might happen that you want both tables in a join to be optional. Perhaps you want to list all customers, and all persons, joining them where possible, and you want the entire listing in a single report. Enter the full outer join:
SELECT c.CustomerID, p.FirstName, p.LastName FROM Sales.Customer AS c FULL JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID CustomerID FirstName LastName ----------- ----------- ----------- 29485 Catherine Abel 29486 Kim Abercrombie 29487 Humberto Acevedo ... 1 NULL NULL 2 NULL NULL 3 NULL NULL ... NULL Syed Abbas NULL Kim Abercrombie NULL Hazem Abolrous ...
You won't get the nice groupings that I show if you execute the full outer join in AdventureWorks. I took some license and grouped the rows for the sake of clarity. You will get back the same rows, but you may see them all mixed up rather than neatly grouped.
Results from the preceding full outer join include the following rows:
Putting the query into business terms, I would say that it returns all customers and all persons, correlating customer to person whenever possible.
Earlier in the section on inner joins, I pointed out how the inner join implicitly excluded customers who were not also listed as persons. In that particular case, the business requirement was to include those customers, which led to my introducing outer joins.
But it's not always wrong to implicitly exclude results via an inner join. Think about the business requirement to list products having reviews along with the names of reviewers and their ratings. Here's a query to generate that list:
SELECT p.ProductID, pr.ReviewerName, pr.Rating FROM Production.Product AS p INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID ProductID ReviewerName Rating ----------- ------------ ------ 709 John Smith 5 937 David 4 937 Jill 2 798 Laura Norman 5
The inner join is a perfectly proper choice. The inner join's implicit exclusion of non-reviewed products aligns with the business requirement to list only those products having reviews.
Inner joins are also reasonable when foreign key constraints are in place to ensure that rows in one table have corresponding rows in another. Such is the case when reporting on the names of sales persons. There is a chain of foreign key constraints that require all sales persons to be employees, and all employees to be persons.
Joins are fundamental in SQL, and you'll be hard pressed to find many production queries that are written without at least one join operation. Choose inner joins when the business question you are answering requires corresponding rows in both tables, or when database constraints ensure those same corresponding rows. Write left outer joins when you need to make joins optional in one direction. Question any alleged need for a full outer join, but you will sometimes need them.
If you speak often at conferences, sharing your screen to demo things, this could be helpful:
Throughout your presentation, the audience will be able to see your Twitter Handle, reminding them to include it with tweets about the event. I used to include it in the slides, but this is better, because it works also with live demonstrations where no slides are being showed. Which is incidentally my favorite way to do presentations
Now how can you do it? Quite easy, you open the Windows Control Panel and click on Region and Language. Then click on Additional settings:
Then you insert your Twitter Handle (or any other text you like to see on the taskbar) as AM and PM symbol. Make sure to select Time formats with trailing tt:
That’s it. If you want the font size as large as on the first picture above, that can be done here:
I did that with Windows 7 Professional 64 bit. Hope you find it useful
Tagged: speaker tip
The important thing to understand is the data does not change just because it is managed differently. If the data does not change, then the entities and the relationships contained in the data cannot change either. The entities and the relationships between them have not changed since the dawn of time. They were the same in the days of network database management systems which came before relational database management systems, they stayed the same when object-oriented database management systems came along, and they are the same now that we have NoSQL databases.(read more)
Oracle Corporation openly admits that NoSQL database management systems have the performance advantage over relational database management systems “when data access is ‘simple’ in nature and application demands exceed the volume or latency capability of traditional data management solutions.” Database professionals should therefore look seriously at NoSQL technology.(read more)
Licensing can be a confusing topic for many, but additional stress can be felt for those that use tools that cover multiple products and features that can span more than one management pack.
I’ve demonstrated how you can see what management packs are used and how to control this via EM13c, (also available in EM12c) but that can take you away from the task at hand. That’s where turning on Annotations for Management Packs may be beneficial.
What are annotations and why use them?
http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot2.png?res... 300w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot2.png?res... 768w, http://i2.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot2.png?w=1096 1096w" sizes="(max-width: 435px) 100vw, 435px" data-recalc-dims="1" />
For Enterprise Manager 13c, this results in initials for management packs placed after feature drop down menus throughout the interface.
Turning this feature on is very easy. Click on Settings, Management Pack and then Annotations.
http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot1.png?res... 231w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot1.png?res... 768w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot1.png?w=812 812w" sizes="(max-width: 442px) 100vw, 442px" data-recalc-dims="1" />
Once this is enabled, your drop down menus will look a little different than they did previously, as the annotations will be added for the management pack(s) used by the feature. This is for both the upper right hand drop down from Enterprise to Settings and then the lower left menu, from the main Target Type and across.
To give an example, lets say we’ve logged into a database target and clicked on Performance. We’d now see the annotations for the management packs used for first section of options:
http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot3.png?res... 300w" sizes="(max-width: 242px) 100vw, 242px" data-recalc-dims="1" />
We quickly recognize the Database Diagnostics, (DD) and Database Tuning, (DT) Pack annotations next to each of the features.
Let’s take one of the drop downs from the Enterprise Menu with the annotations turned on. From Enterprise, Configuration, can you tell what management packs are being used outside of DBLM, (Database Lifecycle Management) in the list below?
http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot4.png?res... 300w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot4.png?res... 768w, http://i0.wp.com/dbakevlar.com/wp-content/uploads/2016/04/annot4.png?w=1510 1510w" sizes="(max-width: 518px) 100vw, 518px" data-recalc-dims="1" />
There’s a lot of acronyms and initials there and hint, hint… I already showed you how to find out this information earlier, so take your time, I’ll wait right here….
No tech or management this week – this Friday Philosophy is about something in my home life.
This week we are in Singapore, our first ever visit. The main reason that we have come here is to look at some pictures painted by Sue’s Uncle Stan. They are also called the Changi Murals. Stanley Warren painted these murals when he was gravely ill in Changi during World War 2. He was a POW, captured with the taking of Singapore by the Japanese. Conditions were extremely poor in the POW camps, and across Singapore as a whole. During the occupation thousands died from disease and malnutrition.
Stanley had been a graphic artist before the war and he did some painting whilst he was in the camp of what he saw. He was a deeply religious man and when people knew he could draw his fellow POWs asked him to draw murals on the walls of a chapel they’d built at Bukit Batok. Not long after, he was so ill with amoebic dysentery that he was moved to the Roberts Barracks hospital in Changi, block 151. I don’t think he was expected to live. Whilst he was there, he heard a choir singing in the local chapel for the hospital and his talking to the padre after that led to a request for him to paint some murals on the walls there.
Stanley had to paint the first mural bit-by-bit, he was too unwell to work for more than a few minutes at the start. They also had to use material stolen or obtained as they could. In the first mural there are some areas of blue – that came from a few cubes of billiard cue chalk. He had so little that it ran out after the second mural. The first mural was completed just in time for Christmas and he was carried back up to the wards and could only hear the service from there, no one knowing if the latest bout of dysentery would kill him or not. But it didn’t. Over the next few months Stanley drew four more murals as his health waxed and waned. The amazing things it that, despite the condition he was in, under a brutal regime with very little hope for survival, his message was all about reconciliation. The figures in the murals are from all races and the messages of reconciliation are constant through the murals.
You can read more about Stanley and the Murals at the wikipedia link at the top of this blog, at the RAF Changi association page here or in an excellent book about them by Peter W Stubbs, ISBN 981-3065-84-2
Stanley survived his time as a POW in Singapore and with the end of the war he came home. Stanley is actually Sue’s great uncle – his older sister was Sue’s paternal grandmother. After the war he became an art teacher and had a family. As well as being Sue’s great uncle, He also worked in the same school as Sue’s father and she saw a lot of him, so she knew “Uncle Stan” very well. And, of course, she knew all about the murals.
The story of the murals does not stop with the war as, after the war (during the later part of which the murals were painted over with distemper, when it stopped being a chapel) the murals were re-discovered. They became quite well known and there was a search for the original artist. When Stanley was found they asked him to go back and restore them. He was not keen! He’d spent years trying to forget his time and what he had endured as a POW. But eventually he was persuaded and over 20 or so years made three trips back to restore them. He still did not talk about the war much but the Murals are part of the family history. Stanley died in 1992, having lived a pretty long and happy life given where he was during the 1940’s.
Sue has long wanted to see the Changi Murals and, with the lose of her mother 2 years back, this desire to link back to another part of the family has grown stronger. So we organised this trip out to Asia with the key part being to visit Singapore and the Changi Murals.
There is an excellent museum about the history of Singapore during WWII, especially the area of Changi and the locations which were used to hold POWs and enemy civilians, the Changi Museum. It includes the murals. Only, it does not. This is a new museum which was built a few years back and it has a reproduction of the original Block 151 chapel, with all the murals. The reproductions are very accurate we are told and there is a lot of information in the museum – but they are not the originals as drawn by Uncle Stan.
We only really realised this a couple of weeks before we were heading out to Thailand (our first stop) but we felt it was not a problem as almost every web site that mentioned the murals said you could organise to see the original murals. Only, you can’t really. Someone at some point said you could, and maybe then it was easier, but none of the current articles tells you how to request to see the originals. They don’t even give a clue who to ask. They just repeat this urban myth that you can organise to see the originals. The only exception to this is the Changi Museum web site that lists an email to send a request to – but the email address is no longer valid! (prb@starnet…).
We managed to contact the museum and Dr Francis Li tried to help us, but he could not find out the proper route to make the request at first and then hit the problem we later hit – not much response.
After hours and hours on the net, failing to find out who to ask, I contacted a couple of people who had something to do with the Murals. One of them was Peter Stubbs, who wrote the book on the Changi Murals that I mentioned earlier. Peter was wonderful, he got in touch with people he knew and they looked into it and after a couple of days he had found out the correct group to approach – MINDEF_Feedback_Unit@defence.gov.sg. You email them and you get an automated response that they will answer your question in 3 days. Or 7-14 days. It’s the latter. We waited the 3 days (if you have dealt with government bureaucracy you will know you can’t side step it unless you know HOW to side step it) but time was now running out and I sent follow up emails to MINDEF and Mr Li.
Mindef did not respond. But Mr Li did – to let us know he had also had no response from MINDEF and had gone as far as to ring up – and no one seemed to know about how to see the original murals.
So we were not going to get to see the originals, which was a real shame, but out first full day we did go up to the Changi Museum. It was a very good, little museum. The museum is free. We took the audio tours which cost a few dollars but to be honest all the information is also on the displays. There was a lot of information about the invasion by Japan and what happened and the reproductions of the Murals were impressive. They also had some duplicates of some of the press stories about the murals, from local papers as well as UK ones. There are a lot more press stories than the museum show, we know this as there is a collection of them somewhere in Sue’s Mum’s stuff that we have not found yet.
It was quite emotional for Sue of course, and something well worth us doing. It really brought home to us an inclination of what he and the other POWs had gone through, and yet Stanley did these murals of reconciliation and belief. Of course we don’t really know what it was like, nothing like that has happened to either of us – we just got a peep into that horror.
The rules of the museum said “No photographs” – but we ignored this. These murals were the work of Sue’s Uncle Stan! (we noticed several other visitors were also ignoring the rule anyway). Most of the pictures are poor, no where as good as others you can find on the net (most from the originals) but they are important to us. I only include a couple in this blog.
If you wonder what the small picture of a man in a hat is, below the mural, it is one of only two we have by Uncle Stan. He painted this when on a school holiday in Spain with Sue’s dad also. We have no idea who the picture is of!
It is a great shame we did not get to see the original murals in the room in which her great uncle Stanley Warren painted them, as part of the chapel that was so important to people in such awful circumstances. After we got back from the museum we finally received a response from MINDEF. It was a simple refusal to consider granting us permission to see the murals as they only allow it for surviving Singapore POWs (there will be very few of them now) and direct family (whatever that limit is). I can’t help but feel that was a little inflexible of them, even a little heartless, and was applying a blind rule without consideration of the specifics of the situation.
When Sue is next going to Singapore, with me or not, I’ll see if I can make them relent and grant access to Sue to see the originals.
Irrespective, we got to see something of Uncle Stan’s murals, and that was worth all the effort.
Brian Hitchcock’s secret is simple. For twelve years, he has been reading books on Oracle Database. And taking extensive notes. And publishing them in the NoCOUG Journal. My secret is simple. For twelve years, I have been reading the extensive book notes that Brian has been publishing in the NoCOUG Journal. I’m the editor of the NoCOUG Journal but that’s not the point. It’s that simple.
Straight after the Oracle University Expert Summit in Berlin – which was a big success, by the way – the circus moved on to another amazing place: Istanbul!
The Turkish Oracle User Group (TROUG) did its annual conference in the rooms of the Istanbul Technical University with local and international speakers and a quite attracting agenda.
Do you recognize anyone here?
I delivered my presentation “Best of RMAN” again like at the DOAG annual conference last year:
Many thanks to the organizers for making this event possible and for inviting us speakers to dinner
The conference was well received and in my view, it should be possible to attract even more attendees in the coming years by continuing to invite high-profile international speakers
My special thanks to Joze, Yves and Osama for giving me your good company during the conference – even if that company was sometimes very tight during the car rides