Tech

Structured Query Language (SQL) Overview

image

Abraham Enyo-one Musa Hacker Noon Profile Photo

@abseejpAbraham Enyonemsa

Data Scientist and Technical Content Writer

Structured Query Language (SQL) is one of the most popular programming languages ​​that allows you to store, retrieve, manipulate, and organize data in relational databases such as MySQL, Oracle Database, and Microsoft SQL Server.

This document contains an example of a short SQL query that combines two tables using two unique keys that exist in both tables.

SQL query

image

Broadly speaking, the SQL code example above is “Select all columns from tables a and b and join both tables wherever they match the sk_applicant and hellofresh_week fields. If the keys do not match, a null value will be returned.“Let’s take some time to understand what the query is doing.

In SQL, “select“” Is a reserved keyword used to retrieve and extract data from one or more database tables.

Whenever you use the SELECT keyword in a query, the column you select usually follows (In the SQL code example, “SELECT a. *, B. *” Gets all the columns in tables a and b.).

Database tables store data in the form of logically organized rows and columns and use identifiers (“That is, the primary key and the foreign key) Uniquely identifies table records and enhances references to other related tables.

In SQL, you cannot select data in vacuum. You should always specify the table from which you want to retrieve the data. The query example fetches data from tables a and b. Another caveat is that you can select a column from one table and use keywords like the following to join the data retrieved in another table. Inner bond, left outer bond, right outer bond, complete outer bond, etc.

In the query example, “Full outer join.“FULL OUTER JOIN returns both matching rows and unmatched rows and columns from” Tables a and b “. If the “table b” in the join clause of the records in “table a” does not match, SQL fills the corresponding cell. Use “NULL”. The same thing happens if the record in “table b” does not match the record in “table a”.

Traditionally, programmers have preferred to join tables using a single unique identifier or key. However, the sample query uses two different identifiers for the merge (That is, sk_applicant and hellofresh_week).

The second index further refines the data so that sk_applicant and hellofresh_week in “table a” and sk_applicant and hellofresh_week in “table b” are uniquely matched. This is very important because one applicant can have multiple hellofresh_week values.

The following figure gives an overview of how the code works.

image

However, there are some concerns with SQL queries.

  1. The table names do not describe the data they hold.
  2. The query example uses a thorough column retrieval method. This can be very costly and cumbersome for tables with a large number of columns, especially if you only need to select a small number of columns.
  3. If there are not too many matches for the selected key in the two tables, the percentage of zero values ​​in the retrieved merged table will be high.

To improve your query, you can:

  1. Name “a and b“” Should be replaced with a name that describes the type of data in each table.
  2. If you only need to select a few columns, it is better to list the columns rather than using “”.a. * And b. *“.
  3. After careful consideration of the code, I think inner joins can work more efficiently and effectively than full outer joins.
  4. If you need to select all table columns, a better alternative to the original code example is to use a single “*” to get all the data from both tables, as shown in the code block below. Is to get.
  5. image

    First seen here.

tag

Join the hacker noon