LIKE OPERATORS AND JOIN IN SQL

Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting data based on a specified pattern match. For example, finding everyone named Peace on Park Street.

 SELECT *

    FROM [Park].[Street]

    WHERE Description LIKE’%Pea%’

   SELECT *

   FROM [Dimension].[City]

   WHERE City LIKE’%ton%’

NOT LIKE OPERATORS is direct opposite of LIKE Operators.

When you want to see the words or names that begin with A

We used the wild card at the front of letter A

Example; Select all Names that begin with letter A

SELECT *

FROM [Student_information]

WHERE Name LIKE ‘A%’

Similarly, we used wild card before letter B to see the word or names that end with B.

SELECT *

FROM [Student information]

WHERE Name LIKE ‘%B’

SELECT *

FROM [Student information]

WHERE Name LIKE ‘%d%’ (this is used when you want to see all names or words that contain letter D).

JOIN

A join clause in the Structured Query Language (SQL) combines columns from one or more tables into a new table.

A join stitches two tables and puts on the same row records with matching fields: It is a way of joining two or more tables together to fetch related data.

The SQL JOIN is a command clause that combines records from two or more tables in a database. It is a means of combining data in fields from two tables by using values common to each table. If you’re working with databases, at some point in your work you will likely need to use SQL JOINs.

TYPES OF JOINS

 INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS.

1). Join (Inner Join) returns rows that have matching values in both tables.

Example; 1)

SELECT *

FROM [Fact].[Order]

 JOIN [Fact].[Sale]

ON [Fact].[Sale].[City Key] =[Fact].[Order].[City Key]

2). SELECT *

FROM [dbo].[Orders]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].OrderID = [dbo].[Orders].OrderID

JOIN [dbo].[Product]

ON [dbo].[Product].CookieID=[dbo].[Order_Product].CookieID

JOIN [dbo].[Customers]

ON [dbo].[Customers].CustomerID = [dbo].[Orders].CustomerID

3). SELECT *

FROM [Fact].[Order]FO

JOIN [Fact].[Sale]FS

ON FS.[City Key] =FO.[City Key]

JOIN [Dimension].[City]DC

ON DC.[City Key]=FO.[City Key]

JOIN [Fact].[Purchase]FP

ON FP.[Date Key] = FS.[Delivery Date Key]

2). Left Outer join returns all rows from the left table with corresponding rows from the right table. If there is no matching row, NULLS are returned as value from the second table.

Example;

1).  SELECT *

 FROM [Dimension].[Customer]

 LEFT OUTER JOIN [Fact].[Stock Holding]

 ON [Fact].[Stock Holding].[Lineage Key]=[Dimension].[Customer].[Lineage Key]

3). Right outer join returns all rows from the right table with corresponding rows from the left table. If there is no matching row, NULLS are returned as value from left table.

Example;

SELECT *

 FROM [Dimension].[Customer]

 RIGHT OUTER JOIN [Fact].[Stock Holding]

 ON [Fact].[Stock Holding].[Lineage Key]=[Dimension].[Customer].[Lineage Key]

4). Full outer join returns all rows from both tables. If there is no matching row in the second table, NULL is returned.

Example;

SELECT *

 FROM [Dimension].[Customer]

LEFT OUTER JOIN [Fact].[Stock Holding]

 ON [Fact].[Stock Holding].[Lineage Key]=[Dimension].[Customer].[Lineage Key]

5). Cross join returns all possible combinations of rows from both tables.

Difference between Outer Join and Cross Join

 The main difference between “OUTER JOIN” and “CROSS JOIN” is in the number of rows that are returned. The “OUTER JOIN” command will return all rows from the first table, even if there are no matching rows in the second table. The “CROSS JOIN” command will return the Cartesian product of the two tables, which is every possible combination of rows from the two tables. So, the “OUTER JOIN” command only returns the rows that have matching values, while the “CROSS JOIN” command returns every possible combination of rows.

A “FULL OUTER JOIN” combines the “LEFT OUTER JOIN” and “RIGHT OUTER JOIN” commands. It returns all rows from both tables, regardless of whether there are any matches in the other table. So, all rows from the first table will be returned, with null values in the specified fields for any rows that have no match in the second table. And all rows from the second table will be returned, with null values in the specified fields for any rows that have no match in the first table.

In some cases, having null values in your dataset can make it more difficult to draw accurate conclusions from your analysis. For example, if you’re trying to calculate the average price of a product, but some of the product prices are missing, your average will be skewed. So, it’s important to consider whether missing values will affect the accuracy of your analysis. In other cases, you may be able to ignore the null values and still get useful results. It depends on the specific situation and the type of analysis you’re performing.

When you’re performing an analysis of aggregated data (like calculating an average, sum, or count), and you have some null values in your dataset, there are a few things you can do. One option is to exclude the rows that have null values from your analysis. This can help to ensure that your results are more accurate. Another option is to replace the null values with a placeholder value, like “NA” or “0”. This can allow you to include all of the rows in your analysis, but it’s important to remember that this will change the values of the data.

The “INNER JOIN” command is the most commonly used type of “JOIN” command. That’s because it’s the simplest and most straightforward way to join two tables together. It returns only the rows that have matching values in the specified fields, and it doesn’t create any new rows. In addition, “INNER JOIN” is the default type of “JOIN” command, so it doesn’t need to be specified explicitly. So, it’s the easiest to use and the most commonly used type of “JOIN.