work hard & thinking!

SQL Dasar: Queri dari beberapa Tabel

Kadang-kadang sulit untuk mengetahui apa sintaks SQL yang digunakan ketika menggabungkan data yang meliputi beberapa tabel. Di bawah ini akan dibahas beberapa metode yang sering digunakan untuk mengkonsolidasikan query pada beberapa tabel ke dalam pernyataan tunggal.

SELECT

Yang sederhana adalah pernyataan SELECT merupakan cara paling dasar untuk query beberapa tabel. Anda dapat memanggil lebih dari satu tabel dalam klausa FROM untuk menggabungkan hasil dari beberapa tabel. Di bawah ini merupakan contoh tentang cara kerjanya:

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

Dalam contoh ini, digunakan notasi titik (table1.column1) untuk menentukan kolom tabel itu berasal. Jika kolom tersebut hanya muncul di salah satu tabel yang dituju, Anda tidak perlu menyertakan nama yang memenuhi syarat, tetapi mungkin berguna untuk melakukannya untuk kemudahan dibaca.

Tabel-tabel dalam clausa FROM digunakan pemisah koma. Anda dapat menyertakan sebanyak tabel yang diinginkan, kendati demikian beberapa database memberikan batasan agar secara efektif menanggulanginya sebelum digahungkan dengan statemen JOIN seperti yang dibahas di bawah.

sintak ini secara sederhana merupakan penyederhanaan dari INNER JOIN. Beberapa database memperlakukan sama seperti halnya statemen JOIN. Klausa WHERE memberitahukan database bagian field mana saja yang berhubungan, dan mengembalikan hasilnya jika tabel ada yang dikombinasikan kedalam satu tabel berdasarkan pada kondisi yang disebutkan. Data tidak akan ditampilkan jika kondisi yang dikomparasikan tidak memiliki hasil nilai yang sama. Pada contoh di atas, table1.column1 dan table2.column1 digunakan untuk mengkombinasikan dua tabel, table2.column2 mengembalikan hasil.

You can extend this functionality to more than two tables using AND keywords in the WHERE clause. You can also use such a combination of tables to restrict your results without actually returning columns from every table. In the example below, table3 is matched up with table1, but I haven’t returned anything from table3 for display. I’ve merely checked to make sure the relevant column from table1 exists intable3. Note that table3 needs to be referenced in the FROM clause for this example.
SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1;

Be warned, however, that this method of querying multiple tables is effectively an implied JOIN. Your database may handle things differently, depending on the optimization engine it uses. Also, neglecting to define the nature of the correlation with a WHERE clause can give you undesirable results, such as returning the rogue field in a column associated with every possible result from the rest of the query, as in a CROSS JOIN.

If you’re comfortable with how your database handles this type of statement, and you’re combining two or just a few tables, a simple SELECT statement will meet your needs.

JOIN
JOIN works in the same way as the SELECT statement above—it returns a result set with columns from different tables. The advantage of using an explicit JOIN over an implied one is greater control over your result set, and possibly improved performance when many tables are involved.

There are several types of JOIN—LEFT, RIGHT, and FULL OUTER; INNER; and CROSS. The type you use is determined by the results you want to see. For example, using a LEFT OUTER JOIN will return all relevant rows from the first table listed, while potentially dropping rows from the second table listed if they don’t have information that correlates in the first table.

This differs from an INNER JOIN or an implied JOIN. An INNER JOIN will only return rows for which there is data in both tables.

Use the following JOIN statement for the first SELECT query above:
SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2
ON table1.column1 = table2.column1;

Subqueries
Subqueries, or subselect statements, are a way to use a result set as a resource in a query. These are often used to limit or refine results rather than run multiple queries or manipulate the data in your application. With a subquery, you can reference tables to determine inclusion of data or, in some cases, return a column that is the result of a subselect.

The following example uses two tables. One table actually contains the data I’m interested in returning, while the other gives a comparison point to determine what data is actually interesting.
SELECT column1 FROM table1 WHERE EXISTS ( SELECT column1 FROM table2 WHERE table1.column1 = table2.column1 );

One important factor about subqueries is performance. Convenience comes at a price and, depending on the size, number, and complexity of tables and the statements you use, you may want to allow your application to handle processing. Each query is processed separately in full before being used as a resource for your primary query. If possible, creative use of JOIN statements may provide the same information with less lag time.


JOIN statements and subqueries

For a more detailed explanation of JOINS and concepts that can be used with them, read the articles  “Basic and complex SQL joins made easy” and “Master joins with these concepts.” For more information about subqueries, read “Use SQL subselects to consolidate queries.”


UNION
The UNION statement is another way to return information from multiple tables with a single query. The UNION statement allows you to perform queries against several tables and return the results in a consolidated set, as in the following example.
SELECT column1, column2, column3 FROM table1 UNION SELECT column1, column2, column3 FROM table2;

This will return a result set with three columns containing data from both queries. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used. UNION is helpful when the returned columns from the different tables don’t have columns or data that can be compared and joined, or when it prevents running multiple queries and appending the results in your application code.

If your column names don’t match when you use the UNION statement, use aliases to give your results meaningful headers:
SELECT column1, column2 as Two, column3 as Three FROM table1 UNION SELECT column1, column4 as Two, column5 as Three FROM table2;

As with subqueries, UNION statements can create a heavy load on your database server, but for occasional use they can save a lot of time.

Opsi ganda
Ketika dihadapkan pada query database, ada beberapa cara untuk melakukannya pada masalah yang sama. Hal ini umumnya lebih sering digunakan metode untuk melakukan konsolidasi query pada banyak tabel ke dalam sebuah statemen tunggal. Dimana beberapa dari pilihan ini akan berpengaruh pada performance, latihan-latihan akan sangat membantu anda mengetahui jika dihadapkan pada beberapa macam tipe query.

February 1, 2019 |
Bookmark and Share

Leave a Reply

 

Latest Tweet