Showing entries 1 to 10 of 1220
10 Older Entries »
Displaying posts with tag: sql (reset)
Becoming Familiar With the Little Known SQL Keyword LATERAL

Working on a weekend project,  I became familiar with the SQL keyword “LATERAL”, which I had not used before because it was only introduced recently in MySQL 8.0.14,  so I wanted to share how it can be used.

Some references on this topic:

Lateral Derived Tables

LATERAL Derived Tables in MySQL 8.0

The keyword “LATERAL” comes into play when you work with derived tables. The derived tables have been available in MySQL for a long time, and schematically they look like this:

SELECT t1.foo, t2.bar FROM t1, (SELECT bar FROM table2 WHERE <condition> ) t2 WHERE t1.id=t2.id;

The table “

(SELECT bar FROM table1 …
[Read more]
A must-know about NOT IN in SQL – more antijoin optimization

I will try to make it short and clear: if you are writing SQL queries with “NOT IN” like
SELECT … WHERE x NOT IN (SELECT y FROM …)
you have to be sure to first understand what happens when “x” or “y” are NULL: it might not be what you want!…

Facebook Twitter LinkedIn

SQL EXISTS and NOT EXISTS

Introduction In this article, we are going to see how the SQL EXISTS operator works and when you should use it. Although the EXISTS operator has been available since SQL:86, the very first edition of the SQL Standard, I found that there are still many application developers who don’t realize how powerful SQL subquery expressions really are when it comes to filtering a given table based on a condition evaluated on a different table. Database table model Let’s assume we have the following two tables in our database, that form a one-to-many... Read More

The post SQL EXISTS and NOT EXISTS appeared first on Vlad Mihalcea.

A new, simple way to figure out why your recursive CTE is running away

In MySQL 8.0.1, we introduced support for recursive common table expressions (CTE). There are quite a few blog entries showcasing the feature, starting from this one, and there is also a complete documentation. Today, I would like to present a solution to a problem which nearly everybody meets when writing queries with recursive CTE’s: when infinite recursion happens, how to debug ?…

Facebook Twitter LinkedIn

Antijoin in MySQL 8

In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query. The query was executing 20% faster than in MySQL 8.0.16. This improvement is because of the “antijoin” optimization which I implemented. Here is its short mention in the release notes:

“The optimizer now transforms a WHERE condition having NOT IN (subquery), NOT EXISTS (subquery), IN (subquery) IS NOT TRUE, or EXISTS (subquery) IS NOT TRUE internally into an antijoin, thus removing the subquery.”

Facebook Twitter LinkedIn

A beginner’s guide to SQL CROSS JOIN

Introduction In this article, we are going to see how a CROSS JOIN works, and we will also make use of this SQL join type to build a poker card game. Database table model For our poker card game application, we have created the ranks and suits database tables: The ranks table defines the ranking of cards, as well as the name and symbol used for each card rank: The suits table describes the four possible categories used by the French playing cards: Cartesian product In the set theory, the Cartesian product... Read More

The post A beginner’s guide to SQL CROSS JOIN appeared first on Vlad Mihalcea.

SQL Upper Function Example | MySQL And SQL Server Upper()

SQL UPPER function is used for converting all the characters in the source string to Uppercase characters. If any number is present in the string, then it remains unaffected. SQL UPPER() function converts a string to the upper-case.

SQL upper() Function

Suppose you have the online shopping website. Customers visit the website and provide the necessary information while creating the login account.

Each customer provides few compulsory information such as first name, last name, phone number, email address, and residential address.

Each customer is different, so you cannot expect the similar format for all inputs.

For example, you get a following entries in an SQL table.

We do not see all words following the consistent pattern.

It does not look good as well if you have to share the report daily to higher management for all newly enrolled customers.

The SQL UPPER …

[Read more]
How to Improve MySQL AWS Performance 2X Over Amazon RDS at The Same Cost

AWS is the #1 cloud provider for open-source database hosting, and the go-to cloud for MySQL deployments. As organizations continue to migrate to the cloud, it’s important to get in front of performance issues, such as high latency, low throughput, and replication lag with higher distances between your users and cloud infrastructure. While many AWS users default to their managed database solution, Amazon RDS, there are alternatives available that can improve your MySQL performance on AWS through advanced customization options and unlimited EC2 instance type support. ScaleGrid offers a compelling alternative to hosting MySQL on AWS that offers better performance, more control, and no cloud vendor lock-in and the same price as Amazon RDS. In this post, we compare the performance of MySQL Amazon RDS …

[Read more]
MySQL EXPLAIN ANALYZE

MySQL 8.0.18 was just released, and it contains a brand new feature to analyze and understand how queries are executed: EXPLAIN ANALYZE.

What is it?

EXPLAIN ANALYZE is a profiling tool for your queries that will show you where MySQL spends time on your query and why.…

Facebook Twitter LinkedIn

SQL LTRIM Function Example | LTRIM In MySQL And SQL Server

SQL LTRIM Function Example is today’s topic. SQL LTRIM function is used for removing all the leading whitespaces or specified characters from the string, i.e. from the left-hand side of the string. In this tutorial, you will learn how to use a MySQL and SQL Server LTRIM() function to remove leading blanks from a string. Oracle/PLSQL LTRIM function removes all specified characters from the left-hand side of a string.

SQL LTRIM Function

The LTRIM() function returns the string after removing leading blanks.

The following shows the syntax of the LTRIM() function.

SELECT LTRIM (input_string, [trim_string])

PARAMETERS

Input_string: It is the string from where the characters have to be removed from the left-hand side.

Trim_string: It is completely optional. It is mainly used for specifying the characters to be removed. If this parameter is omitted, then …

[Read more]
Showing entries 1 to 10 of 1220
10 Older Entries »