SAP ABAP Queries Explained: SELECT, INNER JOIN, DELETE & SQL Query Examples

If you're a SAP developer or are stepping into the realm of ABAP programming one of the most fundamental (and most crucial) abilities you'll require is to write effective database queries. When you're looking to pull records from one table, joining several databases, conducting search conditions, or eliminating outdated data, knowing how to write a good SQL queries in SAP ABAP is a must.

SAP ABAP (Advanced Business Application Programming) makes use of Open SQL, an alternative to the standard SQL that is independent of databases and directly integrated to the ABAP language. This means that you don't have to be concerned about specific syntax for databases — it's as if ABAP manages the abstraction for you.

In this complete guide we'll take you through each major query type using actual examples:

• The SELECT query is part of SAP ABAP
• INNER Join query inside SAP ABAP
• SQL query DELETE in SAP ABAP
• Dynamic SELECT query within SAP ABAP
• LIKE in SELECT query SAP ABAP
• A JOIN query is available in SAP ABAP

Let's take a dive.

What Is Open SQL in SAP ABAP?

Before you write even a single line, it's essential to be aware of the basics. Open SQL is the ABAP built-in connection to the SAP databases layer. It can support the majority of standard SQL operations — such as SELECT, INSERT, MODIFY, DELETE, and INSERT — however it abstracts the actual database (whether the database is SAP HANA, Oracle, or MSSQL).

Open SQL queries include:

• Is a database-independent — is compatible with all supported DB platforms
• Optimized through SAP HANA's ABAP running time for SAP HANA
• Integrated directly into ABAP code, without any special connection configuration

Let's look at every query type in depth.

1 SELECT Query in SAP ABAP

The SQL query that is used in SAP ABAP is the most basic and widely utilized database operation. It extracts information of one or more tables in the database in internal tables, or work areas.

Basic Syntax

SELECT <fields> FROM <table> INTO <target> WHERE <conditions>.

Example: Enter All Records in the Internal Table

DATA: lt_mara TYPE TABLE OF mara,
      ls_mara TYPE mara.

SELECT *
  FROM mara
  INTO TABLE lt_mara
  WHERE mtart = 'FERT'.
LOOP AT lt_mara INTO ls_mara.
  WRITE: / ls_mara-matnr, ls_mara-maktx.
ENDLOOP.

Example: Select Single Record

DATA: ls_kna1 TYPE kna1.

SELECT SINGLE *
  FROM kna1
  INTO ls_kna1
  WHERE kunnr = '0000001000'.
IF sy-subrc = 0.
  WRITE: / ls_kna1-name1.
ENDIF.

Example: Select Specific Fields

DATA: BEGIN OF ls_output,
        matnr TYPE mara-matnr,
        mtart TYPE mara-mtart,
      END OF ls_output.
DATA: lt_output LIKE TABLE OF ls_output.

SELECT matnr mtart
  FROM mara
  INTO TABLE lt_output
  WHERE matkl = '001'.

Best Practices for SELECT Queries

• Always make use of the WHERE clause to avoid complete scans of tables
• Prefer SELECT ... INTO TABLE over SELECT ... ENDSELECT for large datasets
• Utilize as many as n rows in the event that you require only a small set of results
• Always verify the SY-SUBRC following SELECT SINGLE in order to deal with no-data scenarios

2 INNER JOIN Query in SAP ABAP

An INNER JOIN query in SAP ABAP retrieves rows with matching values from both tables. This is by far the most widely utilized type of join and is crucial for the normal database structures of SAP.

Syntax

SELECT a~field1 b~field2
  FROM table1 AS a
  INNER JOIN table2 AS b
  ON a~key = b~key
  INTO TABLE lt_result
  WHERE <conditions>.

Example: INNER JOIN on MARA and MAKT (Material and Material Description)

TYPES: BEGIN OF ty_material,
         matnr TYPE mara-matnr,
         mtart TYPE mara-mtart,
         maktx TYPE makt-maktx,
       END OF ty_material.

DATA: lt_material TYPE TABLE OF ty_material,
      ls_material TYPE ty_material.
SELECT amatnr amtart bmaktx
  FROM mara AS a
  INNER JOIN makt AS b
  ON amatnr = bmatnr
  INTO TABLE lt_material
  WHERE bspras = sy-langu
    AND a~mtart = 'ROH'.
LOOP AT lt_material INTO ls_material.
  WRITE: / ls_material-matnr, ls_material-mtart, ls_material-maktx.
ENDLOOP.

Example: INNER JOIN SELECT Query Across Three Tables

TYPES: BEGIN OF ty_sales,
         vbeln TYPE vbak-vbeln,
         kunnr TYPE vbak-kunnr,
         name1 TYPE kna1-name1,
         matnr TYPE vbap-matnr,
       END OF ty_sales.

DATA: lt_sales TYPE TABLE OF ty_sales.
SELECT avbeln akunnr cname1 bmatnr
  FROM vbak AS a
  INNER JOIN vbap AS b ON avbeln = bvbeln
  INNER JOIN kna1 AS c ON akunnr = ckunnr
  INTO TABLE lt_sales
  WHERE a~auart = 'ZOR'.

Why Use INNER JOIN?

Utilizing an inner join select query in SAP ABAP is far superior to acquiring data from different tables separately and then combining them into ABAP code. Join operations occur at the level of the database and reduces the volume of data sent to the server for the application.

3 Other JOIN Types in SAP ABAP

Even though INNER JOIN is by far the most commonly used method, knowing the different join query kinds within SAP ABAP is crucial for the real-world scenario.

LEFT OUTER JOIN

Returns all records in the left table and matching records from the right. If there's no match the NULLs are returned for the columns of the right table.

SELECT a~matnr a~mtart b~maktx
  FROM mara AS a
  LEFT OUTER JOIN makt AS b
  ON a~matnr = b~matnr
    AND b~spras = 'EN'
  INTO TABLE lt_material.

When to Use Which Join

Scenario	Recommended Join
Data must be present in both tables	INNER JOIN
Master data might not be present in a particular table	LEFT OUTER JOIN
All records are required regardless of whether they match	LEFT OUTER JOIN
Parent-child relationship with a mandatory parent	INNER JOIN

4 DELETE Query in SAP ABAP

A DELETE query within SAP ABAP removes data from tables in databases. It is able to erase a single entry or multiple records based on an IF condition or even records inside an internal table.

Syntax

DELETE FROM <dbtable> WHERE <conditions>.

Example: Delete a Single Record

DELETE FROM ztable WHERE id = '001'.

IF sy-subrc = 0.
  WRITE: / 'Record was successfully deleted.'.
ELSE.
  WRITE: / 'No record was located.'.
ENDIF.

Example: Delete Multiple Records Using WHERE Clause

DELETE FROM zorders
  WHERE status = 'CANCELLED'
    AND created_on < '20230101'.

WRITE: / sy-dbcnt, 'records deleted.'.

SY-DBCNT contains the number of rows that are affected by the DELETE command.

Example: Delete Using Internal Table

You may also erase records by transferring keys through an internal table:

DATA: lt_del TYPE TABLE OF ztable.

" Populate lt_del with records to delete
APPEND VALUE #( id = '001' ) TO lt_del.
APPEND VALUE #( id = '002' ) TO lt_del.
DELETE ztable FROM TABLE lt_del.
WRITE: / sy-dbcnt, 'records deleted.'.

Important Considerations for DELETE

• Always use WHERE conditions to avoid accidental full-table deletes
• Make use of COMMIT WORK following DELETE in ABAP applications that handle the LUW (Logical Unit of Work)
• Conduct tests in development environments and always make sure you have a backup strategy
• Prefer soft deletes (status flag) over hard deletes in production SAP systems

5 Dynamic SELECT Query in SAP ABAP

The dynamic SELECT query built in SAP ABAP lets you create SQL queries at time of execution. This is helpful in situations where the table's name or the fields or the conditions aren't known at creation time — for instance in reports that are generic or frameworks.

Dynamic Table Name

DATA: lv_tabname TYPE tabname VALUE 'MARA',
      lt_result  TYPE REF TO data,
      lr_table   TYPE REF TO cl_abap_tabledescr.

FIELD-SYMBOLS: <lt_data> TYPE STANDARD TABLE.
" Create dynamic internal table
CREATE DATA lt_result TYPE STANDARD TABLE OF (lv_tabname).
ASSIGN lt_result->* TO <lt_data>.
SELECT *
  FROM (lv_tabname)
  INTO TABLE <lt_data>
  UP TO 10 ROWS.

Dynamic WHERE Clause

DATA: lv_where TYPE string.

lv_where = 'MTART = ''FERT'' AND MATKL = ''001'''.
SELECT *
  FROM mara
  INTO TABLE lt_mara
  WHERE (lv_where).

Dynamic Fields Using CL_ABAP_TYPEDESCR

For extremely flexible reporting tools you can also choose dynamically certain fields:

DATA: lv_fields TYPE string VALUE 'MATNR MTART MATKL',
      lv_table  TYPE string VALUE 'MARA'.

SELECT (lv_fields)
  FROM (lv_table)
  INTO CORRESPONDING FIELDS OF TABLE lt_mara
  UP TO 100 ROWS.

When to Use Dynamic SELECT

• Generic ALV Reports that operate across tables
• Enhancement or BAdI implementations
• Framework-level tools and components that can be reused
• Data migration tools that can process tables with different structures

6 LIKE in SELECT Query SAP ABAP

The LIKE operator of the SAP ABAP SELECT query is used to perform pattern matching in WHERE conditions, similar to the LIKE clause in SQL. It is compatible with two wildcard characters:

• % — matches any characters in a sequence
• _ — is a match for any single character

Syntax

SELECT *
  FROM <table>
  INTO TABLE <target>
  WHERE field LIKE '<pattern>'.

Example: Find All Materials Beginning with 'MAT'

DATA: lt_mara TYPE TABLE OF mara.

SELECT *
  FROM mara
  INTO TABLE lt_mara
  WHERE matnr LIKE 'MAT%'.

Example: Find Customer Names Containing 'TECH'

DATA: lt_kna1 TYPE TABLE OF kna1.

SELECT *
  FROM kna1
  INTO TABLE lt_kna1
  WHERE name1 LIKE '%TECH%'.

Example: LIKE with Escape Character

If your search query includes the characters % or _, you can utilize an ESCAPE clause:

SELECT *
  FROM ztable
  INTO TABLE lt_result
  WHERE description LIKE '50\%' ESCAPE '\'.

This searches for the exact "50%" in the description field.

Combining LIKE with Other Conditions

SELECT matnr mtart matkl
  FROM mara
  INTO TABLE lt_mara
  WHERE matnr LIKE 'RM%'
    AND mtart = 'ROH'
    AND matkl IN ('001', '002', '003').

Performance Note

Utilizing LIKE '%value%' (leading wildcard) hinders the use of indexes for the vast majority of databases. When possible you can use patterns such as LIKE 'value%' (prefix searching) to benefit from databases' indexes.

7 Advanced SELECT Tips in SAP ABAP

Using ORDER BY

SELECT matnr mtart ernam
  FROM mara
  INTO TABLE lt_mara
  ORDER BY matnr ASCENDING.

Using GROUP BY and HAVING

TYPES: BEGIN OF ty_count,
         mtart TYPE mara-mtart,
         cnt   TYPE i,
       END OF ty_count.
DATA: lt_count TYPE TABLE OF ty_count.

SELECT mtart COUNT() AS cnt
  FROM mara
  INTO TABLE lt_count
  GROUP BY mtart
  HAVING COUNT() > 100.

Using Aggregate Functions

DATA: lv_max_qty TYPE ekpo-menge.

SELECT MAX( menge )
  FROM ekpo
  INTO lv_max_qty
  WHERE matnr = '000000000000000001'.

FOR ALL ENTRIES (FAE)

For all entries is a mighty method of filtering a huge table using the keys in an internal table — frequently utilized as a substitute for joins:

DATA: lt_vbeln TYPE TABLE OF vbap,
      lt_vbak  TYPE TABLE OF vbak.

" First: populate the driver table
SELECT vbeln FROM vbap INTO TABLE lt_vbeln
  WHERE matnr = '000000001'.
" Then: use FAE
IF lt_vbeln IS NOT INITIAL.
  SELECT * FROM vbak INTO TABLE lt_vbak
    FOR ALL ENTRIES IN lt_vbeln
    WHERE vbeln = lt_vbeln-vbeln.
ENDIF.

8 Common Errors and How to Fix Them

Error	Cause	Fix
SY-SUBRC = 4 after SELECT SINGLE	There is no record that matches	Add IF sy-subrc = 0 check
Short dump on FOR ALL ENTRIES	Internal table of empty driver	Always check IS NOT INITIAL before FAE
Issues with performance in LIKE	Leading wildcard %value%	Use caution when using leading wildcards; use prefix search
An error at the time of running with dynamic SELECT	Name of table not correct at time of running	Check the table's name against the DD02L
The wrong number of rows were deleted	No WHERE clause in DELETE	Always include WHERE conditions when you DELETE

9 SEO Summary: Key Query Types at a Glance

Query Type	Purpose	Key Clause
SQL query in SAP ABAP	Data read from DB table	SELECT ... FROM ... INTO
INNER JOIN in SAP ABAP	Combining data from two or more tables	INNER JOIN ... ON
Join query in SAP ABAP	All types of joins (inner/outer)	LEFT OUTER JOIN, INNER JOIN
SQL query DELETE in SAP ABAP	Remove the records from DB	DELETE FROM ... WHERE
Dynamic SELECT in SAP ABAP	Runtime-generated SQL	FROM (lv_tabname)
LIKE in SELECT SAP ABAP	Pattern match in WHERE	WHERE field LIKE 'pattern%'
SQL query within SAP ABAP	General term used to describe Open SQL	All of the mentioned

Conclusion

Understanding SQL queries within SAP ABAP is a fundamental skill required by every ABAP developer. If you're writing a simple query using SELECT within SAP ABAP and creating more intricate INNER JOIN queries and implementing dynamic SELECT or a DELETE operation or employing LIKE to match patterns — each query type comes with distinct syntax, use instances, and implications for performance.

At Best Online Career, we believe in providing SAP developers with real-world actual-world experience. The best practices and examples included in this guide will serve as your guide when dealing with database operations within SAP ABAP.

Do you want to know more? Explore some of our SAP ABAP tutorials that cover internal tables, ALV reports, BAPIs as well as SAP HANA optimized ABAP programming.