Of course we can simply add fp.start_date and fp.end_date to our column list and look to see which record is current. However, when I run the query, I get 2 rows for Rachel – one for her current phone number and another for her previous one. My data and yours might now be very different because of all the practising you’ve been doing (you have been practising, haven’t you? These articles might be good, but you may as well be reading a Dan Brown novel if you don’t put what you’re learning into practise). So if you ever notice that your query is returning more rows that you anticipated, look for a Cartesian join. This doesn’t only happen when our Where clause is completely missing the same thing would happen if we were joining 3 tables, but only included 2 in our Where clause or if we joined the tables ambiguously (always join using key columns where possible).
Whenever we don’t tell Oracle how our tables are related to each other it simply joins every record in every table to every record in every other table. What we have here is a Cartesian Product, and you’ve probably already guessed that it has something to do with our missing Where clause. The above query will give you many, many rows that look identical however, if you replace the column list with an asterisk (*) and rerun the query, you’ll notice that the records aren’t exactly identical, each has one column different. You’ve probably already guessed that I’m setting you up, but it’s important that we make these mistakes now, so we can learn about them. The syntax for a multi-table select statement is as follows:įROM FRIEND_NAME, FRIEND_PHONE, PHONE_NUMBER We need a select statement that can query multiple tables at the same time. But hey, you can probably ride a unicycle across Siberia – but that didn’t stop them from inventing the car.
#Query to list all tables in oracle database series
We could, of course, get the information by running a series of queries: one select to find our friends’ names and their friend_id a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. Our friends’ names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from an amoeba to a bee, a butterfly, to Beyoncé.Ĭonsider a real-world requirement that we might have of our Addressbook database since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. In our previous articles we acquainted ourselves with our bricks now it’s time to build. This can be accomplished with a simple SELECT query on the USER_TABLES data dictionary.Oracle for Absolute Beginners: Part 4 – Multi-table queries - Simple TalkĪ wise man* once said: To build the Great Wall of China, you must start with a brick. Viewing Tables Owned by Current userĪt the most basic level, you may wish to view a list of all the tables owned by the current Oracle user. The values in these stored dictionaries are updated automatically by Oracle anytime a statement is executed on the server that modifies data.įrom there, the read-only dictionaries can be read and queried just like any standard table, which as we’ll see below provides some very useful functionality. What are Oracle Data Dictionaries?Ī data dictionary in Oracle is a collection of read-only tables that provide useful information about the database including schemas, users, privileges, and even auditing data. Thankfully, there are multiple ways to perform this relatively simple task in Oracle, so we’ll briefly explore each option below to find which best suits your needs. As with most relational databases, there may come a situation where you need to view the the underlying metadata and look through the actual table list and ownership of your database.
0 kommentar(er)
